Loan amortization schedule for variable interest rates

[Shaun]

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.

 

[Fred Smith]

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.

 

[Bernie Deitrick]

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

 

[MurrayBarn]

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 (e-mail address removed) and I will fix it for
you.

Cheers
Murray

 

[JoeU2004]

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

 

[JoeU2004]

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