Leading the way in Microsoft Office Development

Understanding Loan Repayments

Any loan consists of five important numbers. These are the following:

1) The amount you will have to pay back every month.

2) The total amount that you will have to pay back.

3) The amount that you have borrowed (often referred to as the principal).

4) The amount of interest you are being charged (tyically given annually).

5) The number of times you will have to pay the amount.

The following exercise will be useful if you want to compare different types of loans.

Lets assume it is a 2 year loan for £12,000 with an annual interest rate of 5%.

PMT - How much will I need to pay each month ?

 -

PMT(rate, nper, pv [,fv] [,type])

You can calculate the amount that you will have to pay back every month using the PMT() function.

Enter the total number of payments: 24 in cell "C3"

Enter the annual interest rate: 5% in cell "C4".

Enter the total amount borrowed: £12,000 in cell "C5".

Enter the following formula in cell "C2" to return the monthly payment.

In this case the payments are monthly so the rate must be divided by 12.

The value returned is negative because it represents money going out that you have to pay each month.

FV - What is the total amount I will have to pay back ?

 -

FV(rate, nper, pmt [,pv] [,type])

You can calculate the total amount that you will have to pay back by using the FV() function.

Delete the formula in cell "C2" and type the number 526.46 directly into the cell.

Enter the following formula into cell "C6".

The payments are monthly so the rate must be divided by 12.

The payments must be negative since this is money going out.

PV - How much have I borrowed ?

 -

PV(rate, nper, pmt [,fv] [,type])

You can calculate the original amount that you borrowed by using the PV() function.

Delete the formula in cell "C6" and type the number 13259 directly into the cell.

Delete the value in cell "C5" and type the following formula to return the amount borrowed.

The payments are monthly so the rate must be divided by 12.

The payments must be negative since this is money going out.

RATE - What interest rate are they charging me ?

 -

RATE(nper, pmt, pv [,fv] [,type] [,guess])

You can calculate the interest rate you are being charged by using the RATE() function.

Delete the formula in cell "C5" and type the number 12000 directly into the cell.

Delete the value in cell "C4" and type the following formula to return the interest rate.

This interest rate must be multiplied by 12 to represent an annual interest rate.

NPER - How many payments will I have to make ?

 -

NPER(rate, pmt, pv [,fv] [,type])

You can calculate the total number of payments you will have to make by using the NPER() function.

Delete the value in cell "C3" and enter the formula given to calculate the rate of interest you are being charged.

Delete the formula in cell "C3" and enter this as a number.

Delete the value in cell "C5" and enter the formula given to calculate the total number of months.