Oct 02 2020 08:44 PM
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
Oct 02 2020 10:34 PM
Oct 03 2020 09:56 AM
Yes.
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
Oct 03 2020 09:57 AM
Also, this mailer would pull additional information from the same line item as well, such as Name of borrower, address, etc.
Oct 04 2020 01:21 AM
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.
Oct 04 2020 03:12 PM
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).
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
Oct 04 2020 09:45 PM - edited Oct 04 2020 10:00 PM
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.
Oct 06 2020 08:44 PM
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!!
Oct 06 2020 11:54 PM