Calculate number of months to pay off a loan given an introductory rate

[silentdragonfly]

I am trying to calculate the # of mths to pay off a loan if you have an introductory lower rate for the first X months. Here are my assumptions along with the formulas:

I can't figure out how to calculate cell E4 - the term after the introductory term is over, which is 6 months.

Thanks in advance,
Peg

 

[Dave Patton]

How many month did you calculate when you prepared an amortization schedule?
N.B. You can post an extract of your sheet to the forum with the forum's tool XL2BB.

Loan Amortization.xlsm
	A	B	C	D	E
1
2	Loan				3000
3	Term 1				6
4	Term 2				32
5	Rate 1				3.99%
6	Rate 2				9.50%
7	Payment				90
8
Loan term

Cell Formulas
Range	Formula
E4	E4	=ROUNDUP(NPER(E6/12,-E7,FV(E5/12,E3,E7,-E2,0),0,0),0)

 

[Dave Patton]

What did you calculate for total interest?

Is the total interest 394.92?

394.92​

394.92​

 

[silentdragonfly]

@DavePatton I didn't prepare an amort schedule. I was trying to do it with just formulas. Is that my problem?
Also I can't calculate total interest because I don't know the total number of months for the term. It's like a catch22.
I tried but my work firewall won't let me download XL2BB.

 

[Dave Patton]

Did you try the suggested solution?

Loan Amortization.xlsm
	A	B
4	Term 2	31.72033
Loan term

Cell Formulas
Range	Formula
B4	B4	=NPER(B6/12,-B7,FV(B5/12,B3,B7,-B2,0),0,0)

N.B. You can copy post #2 to a clean sheet. Click the icon below the f(x) in the header and paste to your sheet.

 

[silentdragonfly]

Thank you @DavePatton! Worked exactly as needed.
Enjoy your Labor Day weekend!