MurrayBarn said:
Interest Payment:- use the formula IPMT
Interest payment can be computed by: previous balance times periodic
interest rate.
Capital Payment:- use the formula PPMT
Principal payment can be computed by: payment minus interest payment.
You can also use PMT is calculate your monthly repayment which is also
IPMT
and PPMT added together
Payment should be computed by PMT rounded appropriately, or it is the
periodic payment specified by the lender. The lender is not obligated to
use the equivalent of PMT; that is simply the most common approach. Many
lenders round or round-up to an integral amount.
Whether the OP uses PMT or PPMT, the computation must be based on two
different loan amounts: (a) the initial loan for the first 12 months; and
(b) the remaining balance after 12 months for the remaining payments. The
remaining balance after 12 months can be computed by
FV(monRate1,12,payment1,2500000), assuming monthly payments. Alternatively,
simply refer to the cell in the amortization schedule that contains the
remaining balance after 12 months.
divide the annual rate of interest by the nu8mber of periods you are using
per annum.
That depends on the jurisdiction of the loan. According to
http://support.microsoft.com/kb/294396/en-us:
(a) For Canadian loans, the monthly rate is RATE(6,0,-1,1+annRate/2), or
(1+annRate/2)^(1/6)-1 if you prefer.
(b) For UK loans, the monthly rate is RATE(12,0,-1,1+annRate), or
(1+annRate)^(1/12)-1 if you prefer.
NOTE: I found one online UK loan calculator that computed the
monthly payment for a 100,000 loan at 6% for 25 years by
PMT(6%,25,-100000)/12. Assuming that retires the loan after the full 25
years, the effective monthly rate would be RATE(25*12,payment,-100000). I
do not know if the calculator was correct or representative.
----- original message -----