Get startedGet started for free

Cumulative financial functions

1. Cumulative financial functions

When you created that first dashboard, you made sure that your end balance was zero; that's how we know that the formulas were all set up properly! But what happens if the balance at the end of the loan doesn't equal zero? Does it always mean that the formulas are wrong? Not if our loan and amortization periods aren't the same! In this section, we'll be discussing the cumulative financial functions to audit balances on our schedule.

2. Loan length is not amortization length!

On our schedule so far, we have only entered a 5 year amortization length, and we've shown all 60 monthly installments on our loan from start to finish. But what if our loan was a much longer loan - say, a 30 *year* mortgage instead of a 5 year student loan? Interest rates have fluctuated wildly over a long period, and lenders would charge very high rates to ensure that they do not lose money on their investment. The amortization period must always be the time that the scheduled payments will take to reduce the balance to zero. One method to help control that risk is by having a shorter contract period on the loan than the full amortization period. Lenders are able to offer rates closer to their cost of borrowing the funds since there is less risk of interest rate increase in the future. At the end of the loan, the borrower can either pay off the remaining unamortized balance, or enter into a new loan contract with new payments at a new rate. And since a balance can never go below zero, the loan length can never exceed the amortization length.

3. What are cumulative calculation functions?

A cumulative calculation function provides a balance of all interest or principal payments made up to a specified period. So, if you want to know the total principal balance paid on a 5 year loan paying monthly at the end of year 4, you would use a cumulative function from period 1 to period 48. No need to create a whole amortization schedule! In the case of our 30 year amortizing mortgage, if we wanted to know what the balance is at the end of our 5 year loan, we could use cumulative functions to calculate the figure, then verify against our schedule.

4. The CUMPRINC() and CUMIPMT() functions.

The cumulative interest and principal functions, CUMPRINC() and CUMIPMT() should look very familiar. Other than the first period and last period arguments, these are the same as the IPMT() and PPMT() formulas that we have already been working with for our schedule! Rate, number of periods and present value work the same way as the periodic functions, and the end or beginning optional parameter can still safely be ignored. The first period is the first period that we are looking to accumulate from. Normally, this will be period 1. If we are calculating a second loan, or if we're trying to find out what our interest paid over a year is - say to take a mortgage interest deduction, that can change. The last period is the final period where interest is calculated. So, for a 5 year loan paying monthly, this would be period 60.

5. Time to accumulate some experience with cumulative functions!

Now it's time for you to get some practice with the cumulative interest and principal functions!

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.