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

silentdragonfly

New Member
Joined
Dec 19, 2014
Messages
28
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:

1661979000471.png


1661979060576.png


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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
ABCDE
1
2Loan3000
3Term 16
4Term 232
5Rate 13.99%
6Rate 29.50%
7Payment90
8
Loan term
Cell Formulas
RangeFormula
E4E4=ROUNDUP(NPER(E6/12,-E7,FV(E5/12,E3,E7,-E2,0),0,0),0)
 
Last edited:
Upvote 0
What did you calculate for total interest?

Is the total interest 394.92?
394.92​
394.92​
 
Upvote 0
@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. :(
 
Upvote 0
Did you try the suggested solution?

Loan Amortization.xlsm
AB
4Term 231.72033
Loan term
Cell Formulas
RangeFormula
B4B4=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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top