| Loan repayments | Detailed Index |

Excel Financial functions and formulas

Excel contains many financial functions and this page includes examples of PMT, PPMT, FV and IPMT. These calculate compound interest, loan repayments and outstanding principal amounts. Many other functions are only available if you load special add-ins after installing the Analysis Toolpak (see Help | Financial Functions). Also included on this page are some formulas with which values can be calculated without using built in functions.

The following example shows the results of various financial calculations based upon both Annual and Monthly values. Note that the cells C4:D8 have all been given names ending with '_A' (annual) or '_M' (monthly). The names have been used in the formulas rather than the cell references.

- B C D E
2 Financial Calculations      
3   Annual Monthly Fld names
4 Amount 28000 28000 Amt_M
5 Rate 0.06 0.005 Rate_M
6 Periods 5 60 Per_M
7 Savings 1200 100 Sav_M
8 Current period 2 24 Now_M
10 Compound interest 37470.32 37767.80  
11 Loan repayments C 6647.10 541.32 formula
12 Loan repayments F -6647.10 -541.32 function
13 Princ repaid this period -5265.13 -450.10  
14 Int repaid this period -1381.97 -91.22  
15 Value of regular saving -6764.51 -6977.00  
16 Loan OS at current period 17767.78 17793.69  

Formulae used in the example
10 =Amt_A*((1+Rate_A)^Per_A) compound interest @ 6% over 5 years
11 =(Rate_A*100*Amt_A*((1+Rate_A)^Per_A)) / (100*(((1+Rate_A)^Per_A)-1)) a formula for regular loan repayments
12 =PMT(Rate_M,Per_M,Amt_M) a function for regular loan repayments
13 =PPMT(Rate_A,Now_A,Per_A,Amt_A) the principal repaid during year 2 of a loan
14 =IPMT(Rate_M,Now_M,Per_M,Amt_M) the interest element of the current repayment
15 =FV(Rate_M,Per_M,Sav_M) the value in 60 months of saving £100pm @ 0.5% (or 6%pa)
16 =(Amt_M*((1+Rate_M)^Now_M))
     - ((-D12/Rate_M)*(((1+Rate_M)^Now_M)-1))
the amount still outstanding after 24 months. Note that cell D12=monthly repayment

These formulas all assume a steady rate of interest. This is unlikely over a number of years and so the calculations may need to be broken down into several separate elements, each using different rates. [See how ...]

Using the copy and paste functions on a worksheet it is easy to check these results by creating a long table with the cumulative and monthly principal and simple interest calculations on each row.

A detailed step by step example of calculating loan or mortgage repayments
Excel 2010 version Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this 
file. It has been rewritten for Excel 2010

file: xlfinancial.htmLast updated Jan14 2016 CMIDX S1 P2 Y