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.

Here are the steps to create a loan amortization schedule for variable interest rates using Microsoft Excel:

1. Open a new Excel spreadsheet and create the following headers in the first row: Payment Number, Payment Date, Beginning Balance, Payment, Interest, Principal, and Ending Balance.
2. In the second row, enter the initial loan amount of 2500000 in the Beginning Balance column.
3. In the Payment Number column, enter the numbers 1 through 156 (13 years x 12 payments per year).
4. In the Payment Date column, enter the dates of each payment. You can use the Excel formula "=DATE(YEAR(start_date),MONTH(start_date)+n,1) " to calculate the payment date for each month, where "start_date" is the date of the first payment and "n" is the payment number minus one.
5. In the Payment column, enter the formula "=PMT(rate/12,term*12,amount)" to calculate the fixed payment amount for each month, where "rate" is the annual interest rate, "term" is the loan period in years, and "amount" is the loan amount.
6. In the Interest column, enter the formula "=balance*rate/12" to calculate the interest portion of each payment, where "balance" is the beginning balance of the loan for that payment.
7. In the Principal column, enter the formula "=payment-interest" to calculate the principal portion of each payment.
8. In the Ending Balance column, enter the formula "=balance-principal" to calculate the ending balance of the loan for that payment.
9. For the first 12 payments, use the interest rate of 8% in the Interest formula. For the remaining payments, use the interest rate of 10%.
10. Copy the formulas down to all 156 rows to complete the amortization schedule.

That's it! You now have a loan amortization schedule that takes into account variable interest rates. You can customize the formatting and layout of the spreadsheet as desired.

Just roll your own. Amortization tables are easy to do manually. In your
case, you'll need five columns:
Opening balance, interest rate, payment, interest, closing balance.

Post back if you need help on the formulas.

Regards,
Fred.

"Shaun" wrote in message
...
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.

Shaun,

You need to use your template twice - once with 8% for 13 years and your original amount
($2,500,000), then again with that loan's ending balance after 12 months($2,385,956.43) with 10% and
12 years as the rate and length.

HTH,
Bernie
MS Excel MVP


"Shaun" wrote in message
...
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.

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.

"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.

"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.

You might be able to modify the existing template relatively easily. But
without knowing the specific template, it is difficult to offer specific
guidance.

I prefer to develop my own amortization schedule from scratch. It is not
difficult. And it avoids the pitfalls in the MS templates that I have
looked at. In one template, the monthly payment is not rounded as needed.
In another template, the form of the schedule is not what I want.

If it helps, the payment for the first 12 months can be computed by
PMT(8%/12,13*12,-2500000). That should be rounded by either
ROUND(payment,2) or ROUND(payment,0), whichever is appropriate.

The remaining balance after 12 months can be computed by
FV(8%/12,12,payment,-2500000).

The payment for the remaining 12 years can be computed by
PMT(10%/12,12*12,-fv), where "fv" is the remaining balance after 12 months
as computed above. Again, that should be rounded appropriately.

Note that these formulas assume the monthly interest rate is the annual rate
divided by 12. That is appropriate for the US and much of the world. But
Canada and the UK do things differently, according to
http://support.microsoft.com/kb/294396/en-us.

The formulas also assume that the monthly payment is indeed computed in the
manner described above. Although that is common, the lender is not
obligated to do it that way, at least not in the US.


----- original message -----

"Shaun" wrote in message
...
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.