equity loan payments – home

Stephen Nelson asked:




Microsoft Excel can help you down a loan payment into its principal 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 principal component of a payment.

Using the IPMT Function to Calculate Payment Interest

The IPMT function calculates the interest portion of a payment given its interest rate, the
period, the term (or number of payments), present value (or loan balance), future 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 period following the ordinary annuity
convention.

The function uses the following syntax:

IPMT (rate, period, nper, pv, fv, type)

For example, to calculate the period interest rate for the 54th payment on a 30-year, $150,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. Notice that to convert the 8% annual interest to a
period interest, the formula divides the annual interest rate by 12. Notice, too, that to convert
the 30-year term to a term in months, the formula multiplies 30 by 12. The function returns the interest payment amount as a negative value because it reflects a cash outflow you pay.

NOTE If you set the pv argument to -150000, you indicate that you’re loaning money. In this case, the function returns 957.51, a positive value, showing that the interest payment amount is a positive cash inflow.

Using the PPMT Function to Calculate Payment Principal

The PPMT function calculates the principal portion of a payment given its interest rate,
the period, the term (or number of payments), present value (or loan balance), future 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 period following the ordinary annuity
convention.
The function uses the following syntax:

PPMT (rate, period, nper, pv, fv, type)

For example, to calculate the period principal payment for the 54th payment on a 30-year,
$150,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. Notice that to convert the 8% annual interest to a
period interest, the formula divides the annual interest rate by 12. Notice, too, that to convert
the 30-year term to a term in months, the formula multiplies 30 by 12. The function returns the principal payment amount as a negative value because it reflects a cash outflow you pay.

NOTE: If you set the pv argument to -150000, you indicate that you’re actually loaning money.
And in this case, the function returns 143.13, a positive value, showing that the principal payment amount is a positive cash inflow.

Julio