Formulas are one of Microsoft Excel’s most useful tools. Rather than manually compute financial transactions, you can let the software handle them for you and save time.
Using formulas, you can create templates, dashboards, and other helpful spreadsheets for lease and debt management. Write the formulas once, and they’ll automatically adjust as you change the information within relevant input cells.
Excel offers built-in, and you don’t need to master them all to maximize the benefits of this spreadsheet software. Below, we’ve detailed six of the most valuable Excel formulas, including sum, sumproduct, edate, days360, present value, and price— using them makes creating a debt and lease management worksheet easier.
Sum()
The sum() function sums several cells and produces that sum in the same cell as the formula.
It looks like this:
=sum(*cells or cell range you’d like to sum*)
The list of summed cells could be a single row, column, or cells from across multiple rows and columns. You can also pick individual cells and sum them up.
There are plenty of ways to add inputs to this formula:
- Typing in desired cells: For example, typing in “=c7+c8”
- Clicking desired cells individually
- Clicking and dragging for cell ranges
- Clicking and dragging to copy + paste the formula
Excel may autocomplete certain formulas depending on the location of the sum() formula. For example, imagine cells A2-A5 have numbers in them. If you type “=sum” in A6, Excel may automatically fill in [A2-A5].
Sum() Example
Imagine you issue bonds paying a total interest of $5,000 semi-annually. At maturity, you repay the principal of $100,000. You put this principal in a separate cell.
Using the sum() function, you could allow Excel to calculate the total interest you will pay over the bonds’ terms. You could place this sum at the bottom of the payment amounts column, and Excel may fill in the formula for you once you type “=sum(” into the cell.
To calculate just the interest, you’d sum the whole column and adjust the formula to subtract the principal repayment. Otherwise, you’d leave out the subtraction to calculate the total amount paid.
Sumproduct()
The sumproduct() function is great for adding rows multiplied against each other.
The sumproduct() function allows you to sum up columns/rows after calculation in another column/row. It creates a hypothetical column/row that calculates the column/row separately, then sums the hypothetical column/row to calculate the answer.
It looks like this:
=sumproduct(*cell range*, *cell range*)
Sumproduct() Example
Say you issue five bonds with the same par values and different coupon rates. Each pays semi-annually on the same date every year, and all mature on the same date.
You want to calculate the total interest paid on all the bonds at each payment date.
You’d give each bond a separate row, then make columns for bond par value and coupon rate, then fill in each bond’s par value and coupon rate in the corresponding row.
From there, you’d put your sumproduct() formula at the bottom.
Now, let’s say your par value column stretches from B3 to B9, and your coupon rate column from C3 to C9.
Here’s how the sumproduct() function would look:
=sumproduct($B$3:$B$9, $C$3:$C$9)
This automatically multiplies each par value by the coupon rate in its row to find that row’s coupon payment, then sums them all up. You skip having to calculate each interest payment in a separate column and then summing that column.
EDATE()
Once a municipality issues a bond, it must make regular interest payments to bondholders. Many of these bonds pay interest semi-annually, meaning every six months.
The EDATE() function permits you to add a certain number of months to a specified day. This function is useful when creating the date column for a specific cash flow with periodic dates. You’ll need an initial date — most likely the issue date — the formula can reference for adding months.
The formula looks like this:
=edate(*issue date cell*, *number of months to add*)
EDATE() Example
As with most municipal bonds, imagine you issue a new bond that pays interest semi-annually.
Instead of doing the math to count six months forward and typing in each date manually, you can use the EDATE function:
As you can see, the user can enter the formula once in A2, then click and drag to fill out the remaining dates automatically.
Days360()
The day count is a convention that determines how you calculate accrued interest on a security, such as a bond or a loan.
One of the most common day count conventions for municipal bonds is 30/360.
This means you assume each month has 30 days for interest calculation purposes — despite February’s 28 days and the 31 days in many other months. Doing this simplifies the calculation by using a nice round number.
Excel’s days360 function will do the date difference calculation for you.
To set up the Days360, you’ll do the following:
- Enter the formula including an open parenthesis “=days360(“
- Enter the cell containing the bond’s issue date in the first spot within the parenthesis
- Enter the cell containing the current date for which you want to find the date difference
- Close the parentheses ” )”
Use the $ sign when clicking and dragging to copy and paste the formula quickly. This will ensure each instance of the formula will reference the issue date instead of the date in the cell below it.
Days360() Example
Imagine you issue a bond on September 1, 2022, that pays interest semi-annually every November 1 and May 1.
Here’s how you would use Days360 to calculate the day count:
Each November 1 and May 1 are listed in descending order.
As for the formula, the issue date is in A1, so this goes in the first part of the parenthetical section. It’s listed as $A$1, so the user can click and drag the formula down the column without changing which cell the issue date portion of the formula references.
The second part of that section has the interest payment date.
Present Value()
Present value says that money now is worth more than money in the future. So it aims to calculate what a given dollar amount would be in the future, having invested that money and earned compound interest.
To do so, it uses a rate called the present value factor.
Here is the present value formula:
From there, you can factor out the future value by dividing it from both sides of the equation. You end up with present value over future value on the left side, which is equal to the present value factor.
Excel’s present value function is limiting. It considers every period equal, meaning every period has the same number of periods.
Since most analyses have at least one period not equal to the rest, you should create a table to calculate the present value.
Present Value() Example
On January 1, 2022, you lease an asset to a lessee at an anticipated 4.00% cost of capital, which is the present value factor. Therefore, the day count basis is 30/360.
Below is this present value calculation within a table.
Next, we have a worksheet showing the formulas that went into the example worksheet above.
As you can see, the days360() function automatically calculates the days from the target date. To calculate the present value factor, apply the present value factor formula, which essentially removes the future value portion of the present value equation.
Finally, we multiply the future values — column B, the revenues we expect to receive — by the present value factor formula to arrive at the present value of those revenues.
Price()
Many factors influence the price of a security.
The price function calculates the price of a security given these parameters. Those parameters include the following, in this order:
- Delivery Date
- Maturity Date
- Interest Rate
- Stated Yield
- Redemption Price
- Interest Payment Frequency
- Basis (Optional – defaults to 30/360)
Since most premium securities are priced to the call date, you must execute two calculations:
- Price-to-maturity
- Price-to-call: The same parameters as price-to-maturity, but replace the maturity date with the call date and set the redemption price to the call price of the security
Once you execute the two calculations, the price of the security is the minimum of the two prices. For an illustration, see rows 10 & 11 in the below example.
Price() Example
You issue several bonds with $100 par values delivered on January 1, 2022. All of them have a 4% coupon rate and pay interest semi-annually.
Each one expires on the same date — July 1 — but two years apart, starting in 2030. Additionally, the earliest maturing bond has a 2% yield, and each subsequent bond’s yield is 0.2% greater than the last.
Here’s what your Excel table may look like:
With the columns appropriately arranged, you can plug the formula into the Price column as follows for row 4:
=price(a4, b4, c4, d4, e4, f4, g4)
Then, click and drag down to copy the formula down the column.