"MurrayBarn" wrote:
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 -----

"MurrayBarn" wrote in message
...
Shaun

The easiest way I have found to do this is to create columns for:
Capital Balance:- Opening balance less capital payments
Months (or years):- running from 1-156
Interest Payment:- use the formula IPMT
Capital Payment:- use the formula PPMT
Interest rate:- use your interest rate below and change it when needed.
(PS
divide the annual rate of interest by the nu8mber of periods you are using
per annum.
You can also use PMT is calculate your monthly repayment which is also
IPMT
and PPMT added together

If you get stuck email it to me on and I will fix it
for
you.

Cheers
Murray
"Shaun" wrote:

I am using a Amortization Schedule template from Microsoft Office online.
While this template is helpful for cases of fixed rate of interest over
the
period, I was looking for a template which would allow for variable rates
of
interest.
My current loan amount is 2500000.
Annual Interest Rate for first 12 months is 8% and for the rest period
would
be 10%. Loan period 13 years, Number of payments per year 12.
Please help me out here.