Interest Rate Calculator

[Columbo_UK]

Hi,

Old user returning with a simple question that I frustratingly can't quite wrap my head around.

I've created an interest rate calculator for a repayment mortgage that takes account of loan size and term, then takes the interest rate and fees into account to spit out what the equivalent rate is, e.g. for a £100,000 mortgage over 25 years, for a 2 year fixed rate of 5.44% with a fee of £495, it has an equivalent rate of 5.78%. I need to do the same calculation for an interest only mortgage, with fees that are a percentage of the loan size, and for the life of me I can't quite grasp what formula to use!? Any help would be appreciated.

 

[awoohaw]

I tried this 2 ways for Amortizing loans (using RATE and IRR functions), and Used IRR to try to figure out Interest only.
I thought they would be the same for the Amortizing loans, so there is something I'm missing. But, maybe this can get you started.
For the RATE formula I used "end of period". For the CashFlow in IRR I used the AMT FINANCED as the first Flow, then 299 interest payments, and the PRINCIPAL as the final flow.

So, while not complete, I hope it gives you some direction. If not, post what you have and maybe some other folks will add.
What calculation did you use to get an equivalent rate of 5.78% for the amortizing loan?
Is equivalent rate the same as APR(Annual Percentage Rate)?

Book1
	A	B	C	D
1			Amortizing	Interest Only
2		Principal	25000	25000
3		Fee	495	495
4		Rate	5.44%	5.44%
5		Term	25	25
6		Calc Pmt	-152.63	-113.33
7		IRR Function	5.6304%	5.5785%
8		RATE Function	5.6461%
Sheet5

Cell Formulas
Range	Formula
D6	D6	=25000*(-D4/12)
D7	D7	=LET(PMTS,1*(TEXTSPLIT(TEXTJOIN(";",,SEQUENCE(,(D5*12)-1,1,0)*(ROUND(-D6,2))),";")), AF,D2-D3,FP,D2,CF,HSTACK(-AF,PMTS,FP),IRR(CF,C4/12)*12)
C6	C6	=PMT(C4/12,C5*12,C2)
C7	C7	=LET(PMTS,1*(TEXTSPLIT(TEXTJOIN(";",,SEQUENCE(,(C5*12)-1,1,0)*(ROUND(-C6,2))),";")), AF,C2-C3,CF,HSTACK(-AF,PMTS),IRR(CF,C4/12)*12)
C8	C8	=RATE(C5*12,C6,C2-C3,,0,(C4/12))*12