An easier way to manage your interest in Excel

These Excel functions will take some of the pain out of calculating your annual loan repayments.

Question

I have used the PMT function to calculate the monthly repayment on a loan. Is there a way to calculate the annual cumulative interest for a loan schedule?

Answer

Figure 1

Figure 1

The CUMIPMT function is designed to do just that. The inputs you used for the PMT function can be used with the CUMIPMT as well as the start and end period numbers.

Figure 1, shows three yellow input cells. These are the minimum requirements to calculate a monthly repayment using the PMT function.

Syntax
PMT(Rate, Nper, PV, FV, Type)

Rate – is the interest rate per period. Interest rates are quoted per annum, so the rate in B3 is divided by 12 to determine the monthly interest rate.

Nper – represents the number of periods for the loan. This is typically entered as a number of years, so we convert that to months by multiplying B4 by 12.

PV – means present value, which is the loan amount.

The PMT function has two optional arguments that have both been omitted from the formula. 

FVoptional – means the future value of the loan. This equates to a balloon payment at the end of the loan period. This defaults to zero when omitted. 

Typeoptional – relates to the timing of the repayment within the period. 1 represents the start of the period and 0 represents the end of the period. If omitted, the end of the period is used.

Figure 2

Figure 2

Cell B8 multiplies the monthly repayment amount by the number of periods to arrive at the total amount repaid (the figure you need to be sitting down to read). Cell B9 calculates the interest paid by adding the loan amount and total repayments together.

Figure 2, has the structure for a loan repayment schedule.

This table will handle loans up to 30 years. The formulas will show zeros for years not used. The formulas in Row 14 have been copied down the rest of the table. Cell E13 is linked to cell B2. The schedule has been formatted to show dollars only.

The interest calculation in cell D14 answers this article’s question.

The formula in D14 is:
 =IF(A14>$B$4,0,CUMIPMT($B$3/12,$B$4*12,$B$2,(A13*12)+1,A14*12,0))

The IF function handles the situation where the year number in column A is greater than the number of years entered in cell B4. If the corresponding row in column A is greater than B4, a zero is displayed; otherwise, the CUMIPMT calculation is performed. 

The CUMIPMT function calculates the total amount of interest paid between two periods. All periods between, and including the start and end periods, are included in the calculation. We need to convert all the period number arguments in the function to month numbers.

The formula is:
CUMIPMT($B$3/12,$B$4*12,$B$2,(A13*12)+1,A14*12,0)

Syntax
CUMIPMT(Rate, Nper, PV, FV, Start_Period, End_Period, Type)

The first three arguments are identical to the first three arguments in the PMT function. The references to the yellow input cells have all been fixed ($ signs added), which stops them changing as the formula is copied down the table.

Start_Period – the first period number for the calculation. The formula uses (A13*12)+1 to calculate this. Column A contains the year numbers. We must convert the year numbers to month numbers. The value in column A from the row above, A13, is multiplied by 12 and then 1 is added. This calculates the first period number for the year on row 14. 

End_Period – the last period number for the calculation. In the formula, it is A14*12, which calculates the last period number for the year on that row.

Type – same as the PMT function, it represents the timing of the payment within the period. 0 represents the end of the month. Zero was the default value for the fifth argument in the PMT function used in cell B6. The Type argument is not optional and so must be entered in the CUMIPMT function. Having calculated the interest for the year, the other calculations on that row are easy.

Figure 4
The B14 formula calculates the annual repayments. It will display zero if the year in column A is greater than the number of years entered in cell B4. The calculation rounds the repayments to two decimal places.

The B14 formula is a little unusual. Many people would have used an IF function similar to the one used in D14. This formula needs to display a zero for all years above the value in cell B4. 

Figure 3

Figure 3

When using a criteria to determine if a zero needs to be displayed, I tend to use logical tests like (A14<=$B$4) wherever possible. This part of the formula returns TRUE when the year in column A is equal to, or less than, the number of years entered in B4. It returns FALSE when the year in column A is greater than the value in B4. 

In Excel, TRUE = 1 and FALSE = 0. Since the formula multiplies the annual repayment calculation by the result of the logical test, it means that when the logical test is TRUE, the repayments are multiplied by 1, which doesn’t affect the calculation. When the logical test is FALSE, the repayment calculation is multiplied by zero and hence is zeroed. 

The totals in row 45 of the table (see Figure 3) all use SUM functions and correspond to the totals calculated at the top of the sheet. (Note: rows 17 to 36 have been hidden in Figure 3.)


Fortnightly repayments

If the loan repayment period is fortnightly, all the references to 12 would be replaced by 26. The revised formulas would be:

Figure 5

The companion Excel file includes the monthly and fortnightly calculations.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.
May 2016
May 2016

Read the May Issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

TABLE OF CONTENTS