Forum Discussion
Calculating a YTD Balance based on end date?
Not sure if this is possible - but wanted to ask.
Need to calculate a "Year-to-date" balance of a savings account while using the TODAY() function as the start date (so it dynamically changes).
Ex:
Total Goal Amt: 5000
End Date: Dec 2026
Total # of Months till completion: 24 (*calculated using TODAY() function + the end date, so dynamically changes - e.g next month it will be 23 months to completion, then 22, and so on)
Displayed as "one value" that changes each month (breakdown shown as an example):
YTD Balance (this month): 208.33
YTD Balance (Dec 2024, next month): 384.61
and so on...
Thanks in advance!
Apologies, I figured out what I was actually looking for can be done using the PMT() function. Thanks for the suggestions though!
3 Replies
Apologies, I figured out what I was actually looking for can be done using the PMT() function. Thanks for the suggestions though!
Try on below:
- Calculate the Total Number of Months till Completion:
- Cell A1: Total Goal Amount
- Cell B1: 5000
- Cell A2: End Date
- Cell B2: 31-Dec-2026
- Cell A3: Today's Date
- Cell B3: =TODAY()
- Cell A4: Months Remaining
- Cell B4: =DATEDIF(B3, B2, "m")
- Calculate the Monthly Savings Required:
- Cell A5: Monthly Savings Required
- Cell B5: =B1 / B4
- Calculate the Year-to-Date Balance:
- Cell A6: Elapsed Months
- Cell B6: =DATEDIF(DATE(YEAR(B2), MONTH(B2), DAY(B2)), B3, "m")
- Cell A7: Year-to-Date Balance
- Cell B7: =B6 * B5
- Calculate the Total Number of Months till Completion:
I am not sure how you expect the first December balance to be 384.61. That would be a smaller payment than the prior month, and if payments continued to get smaller, you'd not get to the goal of $5,000. So maybe you're doing something else.
When I adjusted remaining balance downward and the monthly division downward as well, the result was a consistent 208.33 monthly. See the attached.
So help me understand what I'm missing in your explanation.
