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

 

[slym34]

the 101.55 you mentioned. I assumed it was rounding.

 

[Paella1]

I see. I noticed in your first post you mentioned 6.15% and later you use 6.5%. I'm sure with all the to-ing and fro-ing our scenarios have diverged somewhat. I used 6.5%. I wouldn't be too concerend about the difference. I'm sure there are many.

 

[slym34]

Re: effective rate calculation, interest only loan (SOLVED)

this works great. thanks!