 Home Excel Access | 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 (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 documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file: xlfinancial.htm Last updated Nov17 © meadinkent.co.uk 2017 CMIDX S1 P2 Y