effective rate calculation, interest only loan

[slym34]

Hey all.. I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?

Here is the effective rate calcs on a random normal amortization loan:

this is in B2, and answer is 7%
=RATE(B4*B5,-((B3+B7)/B6),B7)*12

B3 = Total*Interest	  279017.8
B4 = #*Years*in*Loan	  30
B5 = #*Payments*/*Year	  12
B6 = Total*Payments	  360
B7 = Beginning*Principal  200000
B8 - Ending*Balance	  0

problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan and I have no clue how to do that... hoping I'm clear..

Here is the example I'm working on... A client's loan is the following:
Loan amount - 131,538
interest rate - 6.15
30 year amortization
10 years interest only

normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan. Geez I hope this makes sense.. LOL.. Thanks for any help!

John

 

[Glove_Man]

I'm lost.

Seeing as I work in the Quant department of a major bank, that's a bad sign.

The "effective interst rate" is not a term that's strictly defined, as far as I know. If you give me the exact definititon I can figure out how to apply it for you.

A client with a zero interst period at tthe start of a loan has the same interest rate as is quoted (unless there are fees). What happens is that they pay more interest becuase they borrow money for longer. Under any defiintion of "effective interest rate" I can think of, the answer is still 6.15.

 

[Paella1]

Like Glove Man, I'm not sure what you're trying to achieve.

But unlike Glove Man, I never thought there was any ambiguity in the term effective interest rate (though I'm prepared to be convinced otherwise).

I always thought the effective interest rate could be expressed as (1+i/n)^n

 

[Glove_Man]

Paol

That formula converts a monthly or weekly (or anything) compounding rate into an effective ANNUAL interest rate. But it's the annual bit that's important, not the effective. If you trying to compare a number of different options and convert all the different options into effective annual rates then you have a fair comparison.

I have come across "effective interest rate" when there are fees involved. Like if you pay 10% interest only on a loan of $10,000 plus an admin fee of $150 a year, then that's really $1000 + $150 = $1150 = 11.5% of the loan balance each year. So the "effective" interest rate is 11.5%. However, since slym has not mentioned any fees at all, he does not seem to be discussing that topic.

That's where i got to.

 

[slym34]

My version of effective rate is the following. Lets say someone has a 200k loan at 7% for 30 years and it is an interest only loan for 10 years, that person would pay 312,143.32 in interest.. Now lets say I get them to pay additional principal payments throughout the life of the loan, and this caused their total interest to only be 287,873.89. Well this would mean they have paid the same amount of interest as someone with a 6.5% interest only loan. Does this make a little more sense? Escentially I can calculate everything but the interest rate because in a normal amortization schedule, I just put in additional principal payments so it gives me total interest paid, so I want to back into what I am calling the "effective rate" I can do it no problem on a normal amort.. it's the i/o I have a problem with. Thanks!

 

[Paella1]

I can't agree with your reasoning. If a borrower pays additional principal then they pay less interest. But their interest rate - effective, nominal or otherwise - doesn't alter one iota. You would be telling the borrower they are paying a lower "effective" interest rate, but the cost to them is that they will have to pay more principal. The additonal principal they are paying is effectively a cost to the borrower, because they have to part with it in order to believe they have a lower "effective" interest rate. The value of this extra principal/cost would exactly equal the interest difference between a 7.0% loan and a 6.5% loan.

It's smoke and mirrors, without the mirrors and without much smoke. A bit like bundling non-performing mortgages together and pretending that it makes them less risky. But now I'm preachin'.

 

[slym34]

Oh i agree complmetely.. i know it doesn't change squadusha.. and i am not one of those that pretends it does.. however, most clients are not disciplined enough to send extra principal, but if i put it in terms that they can relate to (ie lower interest), they kind of get it, so that's why i offer the calculation.. i just can't do it on an interets only loan, i only know how to do it on a fully amortized loan.. so anyways,, yes you'd be preaching on the bundled loans.. LOL.. but i still want to figure out that calc!

 

[Paella1]

Step 1 - Set up two identical loan schedules for the full 360 period term, but add an additional column for Additional Principal in the first schedule, ensuring any amount in this column comes off the loan balance. Reference the additional payment on every row to the value in a single sell. Put zero in the Additional Principal field for now. Adjacent to both schedules, place the value of total interest paid in a cell. It should be $312,143.32 for now in both cases.

Step 2 - Let's say we have the repayment amount =PMT(0.07/12, 240, 200000) for years 11 to 30 in cell F1. Each row of the schedule will reference this cell using the formula =$F$1. You need to change this formula to: =MAX($F$1, -SUM(B5:D5)) where column B is the opening balance, column C is the interest and column D is the Additional Principal. Similarly, change the Additional Principal so it is the maximum of the negative value of the reference cell and the negative sum of the opening balance and the interest. This is necessary because paying additional principal will pay the loan out sooner. So the schedule needs to stop calculating interest once the balance hits zero (and stop demanding additional principal.

Step 3 - Change the interest rate on the second schedule to 6.5%. The total interest will change to $287,875.11.

Step 4 Use Tools => Goal Seek to set the value in the cell for the total schedule 1 interest to $287,875.11 by changing the value of the Additional Principal cell.

I have done this and found that in this example the required additional principal is $101.55. This is hard to explain, so let me know if you want me to post an example. Happy to send my file to you as well.

 

[slym34]

ok, i'll give that a shot.. i'll figure out the difference, may be rounding? I found round to 2 decimals, seems to help on a lot of the loan stuff i do.. I'll post results tomorrow after I get to the office to try it out. Thanks!

 

[Paella1]

What is the "difference" to which you refer? I had no rounding issues.