Loan Payments with Excel, NPV, IRR: overview
MS excel has financial functions to calculate the loan payments and more. I enter a function simply click a cell, then click "Insert" item of main menu and choose "Functions" item. You can use a PMT function to calculate the loan payment. You can use it if your payments are periodic.
In this sample the formula =PMT(A2;B2;C2;D2;E2) was entered in the cell
F2. A bank offers a $10000 loan at an annual rate of 8% that you must pay off in 10
months.
A rate per month = 8% / 12 = 0.67%.
Where:
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is a sum of the loan.
fv and type are optional parameters.
You can also calculate an internal rate of return for this loan operation. You can use for this purpose the the Excel function named IRR.
The cell H3 contains the formula:
=IRR(C2:C14). The result - 2.42%. An annual internal rate of return is
1.0242 ^ 12 ( months) = 1.33 = 33%.
You can also calculate the Net Present Value. Excel has a function NPV. We entered the formula NPV(E1;C3:C14) + C2 in the cell G2. The result is -2.12.
The function PMT calculates total payment for a period. You can also calculate interest and principal payments. You can use the IPMT function to calculate the interest payment in given period. If a bank offers the three-year $8000 loan at an annual rate of 10%, you can calculate the interest payment for 3 year as following:
The formula IPMT(A2;B2;C2;D2) was entered in the cell G2.
You can use the PPMT function to calculate the principal payment in given period. If a bank offers the three-year $8000 loan at an annual rate of 10%, you can calculate the principal payment for 3 year as following:
The formula PPMT(A2;B2;C2;D2) was entered in the cell G2.
To calculate the total payment for a period, you can use a function PMT.
The formula =PMT(A2;B2;C2;D2;E2) was entered in the cell G2. Please note that an interest plus principal payments are the total payment for a period. In this sample: 295.45 + 2,924.47 = 3,216.92.
Please note that we created special statistical and financial Excel templates to simplify almost any financial and statistical calculations with Excel.
Copyright(C) by FinExcelSoft