PPMT & IPMT

[Budget]

Please review the following formula's and let me know if they are
correct:

I'm trying to calculate the principal payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 1 principal payment per year.

=-PPMT(6.25%,1,15.5,19,855,000)

I'm trying to calculate the interest payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 2 interest payments per year

=-IPMT(6.25,1,15.5,$19,855,000)

I think something's missing.
Please advise.
Thanks

 

[Harlan Grove]

Budget wrote...
....

I'm trying to calculate the principal payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 1 principal payment per year.

=-PPMT(6.25%,1,15.5,19,855,000)

If you have fractional years, they you very likely are making payments
semiannually or more frequently. If so, your formula is wrong.
Generally, the 3rd argument, the number of periods, should be a whole
number. Your interest rate would need to be adjusted to the periodic
basis, that is, your 6.25% rate would need to be converted to the
equivalent semiannual, quarterly or monthly rate.

Also, you can't use commas in your 4th argument, principal, since Excel
will interpret them as specifying additional arguments.

I'm trying to calculate the interest payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 2 interest payments per year

=-IPMT(6.25,1,15.5,$19,855,000)

Big difference between 6.25 and 6.25%. Be careful.

Are there two payments including both principal and interest each year?
Or are there just one principal payment and two interest payments? If
there are two payments including both, and if your 6.25% annual
interest rate were a nominal rate, use

=PPMT(0.0625/2,1,31,-19855000)

and

=IPMT(0.0625/2,1,31,-19855000)

 

[joeu2004]

I'm trying to calculate the principal payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 1 principal payment per year.

"15 1/2 years"? When is the fractional-period payment made: (1) at
the end of the half-year before the 15 annual periods; or (2) at the
end of the half-year after 15 annual periods?

I'm trying to calculate the interest payment for $19,855,000 @ 6.25%
interest rate over 15.5 years with 2 interest payments per year

What exactly do you mean by "interest payments"? Do you mean simply
that interest compounds semiannually? Or do you mean that __you__
actually make a payment mid-year that is equal to interest accrued
during the half-year? In other words, are you making unequal payments
semiannually?(!)

What is the compounding frequency: (1) semi-annually; or (2) something
more frequent (specify)?

I suggest that you clarify the terms of the loan. Specifically, you
omitted the regular (or irregular) payment amount(s); and you omitted
the interest compounding frequency.

 

[Budget]

Thanks. It works.

Budget wrote...
...

If you have fractional years, they you very likely are making payments
semiannually or more frequently. If so, your formula is wrong.
Generally, the 3rd argument, the number of periods, should be a whole
number. Your interest rate would need to be adjusted to the periodic
basis, that is, your 6.25% rate would need to be converted to the
equivalent semiannual, quarterly or monthly rate.

Also, you can't use commas in your 4th argument, principal, since Excel
will interpret them as specifying additional arguments.


Big difference between 6.25 and 6.25%. Be careful.

Are there two payments including both principal and interest each year?
Or are there just one principal payment and two interest payments? If
there are two payments including both, and if your 6.25% annual
interest rate were a nominal rate, use

=PPMT(0.0625/2,1,31,-19855000)

and

=IPMT(0.0625/2,1,31,-19855000)