| | Loan repayments | Detailed Index | | ||
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 (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 |
| 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 |
 |
Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: xlfinancial.htm | Last updated Nov17 | © meadinkent.co.uk 2017 | CMIDX S1 P2 Y |