Microsoft Excel can help you down a loan payment into its essential and interest components. Excel's Ipmt function lets you calculate the interest component of a loan payment. And Excel's Ppmt function lets you calculate the essential component of a payment.
Using the Ipmt Function to Calculate payment Interest
Calculate Mortgage Interest
The Ipmt function calculates the interest measure of a payment given its interest rate, the
period, the term (or whole of payments), present value (or loan balance), time to come value (or
balloon payment), and, optionally, the type-of-annuity switch. If you set the type-ofannuity
switch to 1, Excel assumes payments occur at the beginning of the period, following
the annuity due convention. If you set the annuity switch to 0 or you omit the argument,
Excel assumes payments occur at the end of the duration following the ordinary annuity
convention.
The function uses the following syntax:
Ipmt (rate, period, nper, pv, fv, type)
For example, to calculate the duration interest rate for the 54th payment on a 30-year, 0,000
mortgage charging 8% annual interest, you use the following formula:
=Ipmt(.08/12,54,30*12,150000,0,0)
The function returns the value -957.51. Consideration that to convert the 8% annual interest to a
period interest, the method divides the annual interest rate by 12. Notice, too, that to convert
the 30-year term to a term in months, the method multiplies 30 by 12. The function returns the interest payment whole as a negative value because it reflects a cash outflow you pay.
Note If you set the pv conference to -150000, you indicate that you're loaning money. In this case, the function returns 957.51, a sure value, showing that the interest payment whole is a sure cash inflow.
Using the Ppmt Function to Calculate payment Principal
The Ppmt function calculates the essential measure of a payment given its interest rate,
the period, the term (or whole of payments), present value (or loan balance), time to come value
(or balloon payment), and, optionally, the type-of-annuity switch. If you set the type-of-annuity
switch to 1, Excel assumes payments occur at the beginning of the period, following
the annuity due convention. If you set the annuity switch to 0 or you omit the argument,
Excel assumes payments occur at the end of the duration following the ordinary annuity
convention.
The function uses the following syntax:
Ppmt (rate, period, nper, pv, fv, type)
For example, to calculate the duration essential payment for the 54th payment on a 30-year,
0,000 mortgage charging 8% annual interest, you use the following formula:
=Ppmt (.08/12,54,30*12,150000,0,0)
The function returns the value -143.13. Consideration that to convert the 8% annual interest to a
period interest, the method divides the annual interest rate by 12. Notice, too, that to convert
the 30-year term to a term in months, the method multiplies 30 by 12. The function returns the essential payment whole as a negative value because it reflects a cash outflow you pay.
Note: If you set the pv conference to -150000, you indicate that you're no ifs ands or buts loaning money.
And in this case, the function returns 143.13, a sure value, showing that the essential payment whole is a sure cash inflow.
No comments:
Post a Comment