Loan Payment Calculation with Excel, investment return

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

npv11e.gif (4762 bytes)

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.

npv3e.gif (6820 bytes)

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:

npv4.gif (3887 bytes)

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:

npv4.gif (3887 bytes)

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.

npv4.gif (3887 bytes)

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