MeadInKent
| 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.

8 Hover with mouse to view formulas in this example
B C D E
2 Financial Calculations
3 Annual Monthly Fld names (Col D)
4 Amount 28,000 28,000 Amt_M
5 Rate 0.060 0.005 Rate_M
6 Periods 5 60 Per_M
7 Savings 1,200 100 Sav_M
8 Current period 2 24 Now_M
9 www.MeadInKent.co.uk
10 Compound interest £37,470.32 £37,767.80
11 Loan repayments C £6,647.10 £541.32
12 Loan repayments F -£6,647.10 -£541.32
13 Princ repaid this period -£5,265.13 -£450.10
14 Int repaid this period -£1,381.97 -£91.22
15 Value of regular saving -£6,764.51 -£6,977.00
16 Loan OS at current period £17,767.78 £17,793.69

Formulas 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 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 Nov17 © meadinkent.co.uk 2017 CMIDX S1 P2 Y