spreadsheet help req.

[JFEWSTER]

Any maths teachers out there?

Im trying to get my amortizing spreadsheet to work but its turning out to be more difficult than I first thought

I have a (theroretical) loan of 100k @ 6% for 30 years- P & I. Repayments would be $599.55 per month.
After 5 years, the interest rate jumps to 8%
Is the repayment then going to be $733.76, or $718.21 p/m (or any other figure?)
How is this new figure calculated?

Im thinking that its calculated on the original loan amount, rather than the remaining balance at that time, and over the remaining years left of the loan.

Anyone?

 

[Sim]

Generally the new repayment figure would be calculated such that it is sufficient to pay off the remaining balance over the remaining period given the new interest rate.

The only constant in the equation is the loan term - and by definition of a typical P&I loan, the remaining balance of the loan must be zero by the end of the loan term, regardless of interest rate changes.

 

[michaelg]

Hi,

Its $718.21

30yrs of monthly payments = 360 payments

5yrs of monthly payments = 60 payments.

The balance after the 60th payment @ 6% = $93,054.36

Soooo.....

After the 60th payment there remains only 300 payments left (360-60).

Your new "present value" = $93,054.36

Soooo.....

where the excel PMT function = (RATE,NPER,PV,FV,TYPE)

New PMT = ( 0.08 / 12 , 300 , $93054.36 , 0 , 0 )

or

$718.21

Think of it this way, a rate change is effective you starting a new loan every time.

Michael G

 

[Kevmeister]

Agreed. A change of interest rate can conceptually be viewed as the termination of one loan at the old rate and the introduction of a new loan at the new rate, carrying over the balance from the old loan.

 

[JFEWSTER]

Thanks for the info.
Turns out I'm on the right track.

One other question, what happens when you make an extra payment, (i.e. over and above the regular scheduled amount.)

 

[michaelg]

Hi,

As above, that is, restart your loan calculation, but this time your PV (present value) amount is the previous balance less the amount of that extra payment, I believe.

Michael G

 

[JFEWSTER]

Thanks Michealg

I'm not sure that that is correct because that would mean the loan term would not be any shorter but instead, the monthly payments would be different each time?

Am I correct?

Would I be correct to suggest that the PV needs to NOT include any extra payments so that the loan term reduces instead and if so, how do I enter that into the formula?

 

[michaelg]

Hi,

Hmmmm, I think you're right, not sure then.

Michael G

 

[asy]

try the spreadsheet attached.

I recently wrote it for something else, but I have modified it and think it should work for what you want.

If you need it to be more than 10 years (which you probably will) just copy the cells across.

hope it helps.

asy

 

[asy]

By the way, if you need the interest rate to change, you can do it manually by going in to the first month of year 5 and altering the interest rate...

If you REALLY want it to do it for you, and you are a VERY VERY good boy, let me know and I will write you a programme that does that.

asy

 

[pobrien3]

Whilst we are on the subject of spreadsheets I just thought I would submit one that I designed. This spreadsheet will allow you to enter honeymoon rates or just straight out rates. It will calculate the initial repayments and the repayments for the years after the honeymoon rate has been completed. It will also allow you to compare loans on the basis of total repayments and fees over whatever period you like. Just change the comparison period in cell D2. You can also change any figures in any of the white cells without destroying the formulas.

If you get an error message in the calculated cells just go to the TOOLS menu and select ADD-INS. Then tick the "Analysis ToolPak" and "Analysis ToolPak - VBA" boxes. You will need you Microsoft Office CD to do this.

Regards

Patrick O'Brien