FIguring loan term left, and amortization of loan value remaining

Occasional Contributor

Hey Guys


What I'm looking for is an easy way to take a list of date that has a recorded date (title), and recorded loan amount to figure out how much loan amount "should be" remaining in order to calculate a refinance rate, and savings.


the list I have has over 1700 line items with pretty much all of the same data types, with different values.


What I'm trying to accomplish is a mailer, that auto fills data, and what not based on assumptions of remaining loan amounts, and term remaining...


I may not have explained it correctly, so feel free to ask my more specifics if needed


Excel Formulas and Functions BI & Data Analysis Macros and VBA

8 Replies
Tell me are you looking for loan amortization process and finally wanna to mail all about remaining amount ?



What i want to do is pull information from lets say a loan that was recorded on 2017 with a loan amount of 150,000$, have excel figure out what the amortized amount remaining would be today, and put that on a word postcard mailer...but have it autonomously do it for 1700 other line items as well



Also, this mailer would pull additional information from the same line item as well, such as Name of borrower, address, etc.



Meanwhile I'm posting workbook has sheet calculates loan payments,,, read the instruction before use it. If you want to automate Mail the balance amount,, need Macro, otherwise simply you can use it.

Okay. Lets assume, using your calculator, that todays date is 11/1/2019 (month 10), how can i have excel pull the remaining balance, and input it into a word well as the remaining months available, rounded up to the nearest 5 year *i.e. 5-10-15-20-25-30).


But not on a one by one case, that would be very time consuming, I have a list of 1700 borrowers, and I'd need to pick out remaining balances as of "today" based on their original loan amount, and todays date, using the amortization calculator you provided, using the average Interest rate for the year they closed the loan originally...this interest I would have to put in manually,


so my mailers (in word), would say something like this:

"Hello Mr/Mrs Jones, we see you have a current payment of around (1600$) with (something Bank), with todays interest rates we could save you approximately (400$), Please call ###.###.#### for more information."


The formula I'd need is for excel to pull the remaining balance from the amortization calculator...counting from origination date, to todays date, and then also, hopefully, doing the calculations for the difference in payment based on a calculator of the current interest, and THEIR remaining balance on a different worksheet. 


Also, your worksheet doesnt go past 24 months, how can i fix it? And can i make it do the remaining balance of the months remaining...I.e. 360 total months, 240 paid, input 350 into the No EMI, and it shows the remaining 10 years remaining balance



I think that you have not examined my Loan calculator properly,, please check the Loan Date in cell b10 is 01/01/2019 is the PAST DATE, calculation is for 20 months ,, check the last row the date is 09/01/2020 ,, considering the CURRENT Month is OCTOBER , what else you need,,, look in case of getting PAST value ,, what u have to do is,, workout on paper, before using my sheet,, about the EMI month & No of EMIs according to current date.


And pull the values you need to communicate with client on another sheet and use it with MAIL Merge with MS-WORD!!, Hope this help U.

Thanks a bunch sir!!


I was hoping for some automation of pulling the remaining values, but this will work for me, it will just take some time...


Thanks again!!



Glad to help you,, since the suggested method is working for you,, so if you feel you may accept it as best answer as well like it ,,, and keep asking ☺