Help with formula to automatically perform a function on the same day each month

[Riggs609]

Hi all, I am kind of a newbie to excel and would appreciate your help. I set up a budget sheet and I am trying to figure out a formula that will automatically perform a set function on the same day each month. As example: I want a cell to show the monthly adjusted balance of an auto loan. It would take the starting balance and date from other cells and then deduct the consistent monthly payment amount then subtract the monthly interest rate and display the new monthly adjusted balance. The part I am having trouble with is what formula to use to have the cell automatically perform this calculation on the 15th of every month. Your help would be greatly appreciated! Thank you... Riggs

 

[ranman256]

Well, someone would need to open the workbook,
then at Workbook_Open() event, see if its the 15th, then run the code.
AND youd need a flag to show it has already been run for that date, so you dont run it again when you open the file a 2nd time that day.

OR
use the Scheduler, to open these files on the 15th of every month.
the logic above would remain.

 

[AlphaFrog]

In your "Adjusted Balance" formula, you would have the Number of monthly payments within the formula. Below will calculate the number of months elapsed from the start date to present where A1 is the start date. If the start date is on the 15, the calculation will update on the 15th of each month.

DATEDIF(A1,TODAY(),"m")

Balance
=Amount borrowed - FV(interest rate/12, number of months elapsed,monthly payment)
=Amount borrowed - FV(interest rate/12, DATEDIF(A1,TODAY(),"m"),monthly payment)

 

[Riggs609]

I had made a mistake in my post, whereas I put deduct interest instead of add it, so I just changed your formula from a subtract sign to a plus sign and it worked perfectly. Thank you so much that was a huge help! Riggs

 

[Riggs609]

Thank you!

 

[Riggs609]

Hi, I thought it was working but it wasn't, I noticed you said to add number of monthly payments within the formula but I don't know where to add the 41 months at.

=Amount borrowed - FV(interest rate/12, DATEDIF(A1,TODAY(),"m"),monthly payment)

and this is what I have: =D6 - FV(D4/12,DATEDIF(15,TODAY(),"m"),D2) where D6 is the loan amount, D4 is the interest rate, and D2 is the monthly payment amount it is yielding a super high crazy number not sure what I am doing wrong...thank you!

 

[joeu2004]

Note that Riggs has "moved" this discussion to a new thread at
https://www.mrexcel.com/forum/excel-questions/1043913-formula-fv-date-if-not-working.html .