Forum Discussion
Solved
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). ...
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