FIguring loan term left, and amortization of loan value remaining

Question

Hey Guys&nbsp;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.&nbsp;the list I have has over 1700 line items with pretty much all of the same data types, with different values.&nbsp;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...&nbsp;I may not have explained it correctly, so feel free to ask my more specifics if needed&nbsp;Excel Formulas and Functions&nbsp;BI &amp; Data Analysis&nbsp;Macros and VBA

rajesh_sinha · Answer

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

bigdlizzle · Answer

Yes.&nbsp;&nbsp;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&nbsp;Rajesh_Sinha&nbsp;

bigdlizzle · Answer

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

rajesh_sinha · Answer

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.

bigdlizzle · Answer

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 mailer...as well as the remaining months available, rounded up to the nearest 5 year *i.e. 5-10-15-20-25-30).&nbsp;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,&nbsp;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."&nbsp;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.&nbsp;&nbsp;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&nbsp;Rajesh_Sinha&nbsp;

Forum Discussion

FIguring loan term left, and amortization of loan value remaining

8 Replies

Resources