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

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 Busysoftorder.com