Hello,

I am trying to calculate what the interest rate would need to be after following a shorter term essentially cost the same as as a longer term.

My example is;

$100,000 Principal & Interest over 25 years comparing a 2 year rate 5.00%apr vs a 3 year rate 5.20%apr.
I want to work out what the 1 year rate following the 2 year rate would need to be to break even with the 3 year rate.

Thus far I have been able to compare the CUMIPMT and CUMPRINC formulas of the known rates.
Through this I can calculate the Total payments for the final year, and the additional principal reduction, but when using these figures I keep having a #num! error returned.

I would be grateful for any assistance given...

These are my workings/figures.

PV: $100,000.00
nper: 25 years

Fixed (2yrs) @ 5.00%p.a.

CUMIPMT(5.00%/12,25*12,100000,1,24) = $9,800.86
CUMPRINC (5.00%/12,25*12,100000,1,24) = $4,229.30
TOTAL = $14,030.16

Fixed (3yrs) @ 5.20%p.a.
CUMIPMT(5.20%/12,25*12,100000,1,24) = $15,132.44
CUMPRINC (5.20%/12,25*12,100000,1,24) = $6,334.41
TOTAL = $21,466.85

Differences
TOTAL = $7,436.69
INTEREST = $5,331.59
PRINCIPAL = $2,105.11

In theory

Rate(12,(7436.69/12),(100000-4229.30),(100000-6334.41),0)
However this returns #NUM!

PS the NPER, PV and Fixed rates need to be able to change.