Sep 27 2024 02:32 AM
Hi everyone!
I could really use your help on this, so please bear with my explanation! Thank you so much for your time in advance. This may be a bit of a challenge, but I'm really at a loss as to what formulas are needed, what data is needed to make those formulas work - an ABSOLUTE loss!
I've attached a mock-up I've done that is similar to the real thing. It is one tab of a multi tab spreadsheet that is focused on finances for 2025 - this is the savings tab. During the course of 2025, there are some things that I need to save for, so I've added at the top some examples, with the target date, target to save and then I've worked out how many months I will have to save that (I'm paid on the 15th of the month and the entries from A17 help with this). It then gives a split out as to how much I would need to save each month if I wanted to achieve the target amount by the target date. Hope you're still with me!
I then have an entry for each month, how much disposable income I have to save, how much to pay on each savings goal and then how much disposable income I have left after saving for them.
What I want is that each month it allocates the most money to whatever one I want to save for first (by date), then the next date etc. When one of the savings targets is then met by the date, the following month the extra money then goes into the next target savings goal. For example, if you look at savings goal 3) Car Repair, it wants £65.00 a month to achieve by the target date (15/10/25), but I only have £26.38 available each month. However, that is only until say May or July when the other savings goals are paid off, then I can put the extra money (that I'm not paying towards these) into 3) Car Repair, so it ends up still being paid off by the date, just it won't end up being £65.00 each month, it'll be a lower figure in the first number of months and then increase when the others are paid off.
I really hope this is somewhat understandable!! I want to be able to alter the target amounts, target dates, available to save money, and have everything auto adjust to try and work out a savings plan - what I need to put into the savings amount each month for that goal to try and get it paid off by the target date. If suddenly something changes, such as I have more disposable income or the savings goal is reduced, then the spreadsheet will all automatically update to those changes and then the plan (plan being the best word here) changes. If a savings plan cannot be achieved, as in there isn't enough money available that month to cater for all of the "> Savings", then it flags up and identifies this and tells me that more money is needed. I can then make some changes elsewhere and make sure there is extra available funds (if I need to!)
I really really hope that this is understandable and any advice anyone is able to give is appreciated so so much.
If you have any questions then please do contact me!
Thank you so much again.
Sep 27 2024 12:34 PM
It sounds like you're trying to manage your savings goals dynamically by adjusting your monthly contributions as previous goals are met, reallocating funds towards other goals. To accomplish this, we’ll create a structured Excel spreadsheet that dynamically calculates the savings needed for each goal, reallocates funds when goals are met, and flags if your goals cannot be achieved.
1. Spreadsheet Structure:
We’ll start by laying out the structure of the savings plan. Here’s an outline of what we need:
2. Dynamic Savings Allocation:
We’ll use Excel formulas to automatically allocate your disposable income each month, starting with the savings goal with the earliest target date, and adjust the savings allocation once earlier goals are met.
3. Flagging Issues:
If you don’t have enough disposable income to meet a goal, the formula will flag it so you know you need to adjust your disposable income or target amounts.
Example Spreadsheet Layout:
Savings Goals (Starting in A1)
Savings Goal | Target Amount (£) | Target Date | Months to Save | Monthly Required (£) |
1) Vacation | 3000 | 15/06/2025 | =DATEDIF(TODAY(),C2,"m") | =B2/D2 |
2) Car Repair | 1000 | 15/10/2025 | =DATEDIF(TODAY(),C3,"m") | =B3/D3 |
3) Wedding | 5000 | 15/12/2025 | =DATEDIF(TODAY(),C4,"m") | =B4/D4 |
Monthly Budget (Starting in A7)
Month | Disposable Income (£) | Goal 1 Allocation | Goal 2 Allocation | Goal 3 Allocation | Leftover |
Jan | 500 | =MIN(E2,B2-SUM(F$2:F2)) | =MIN(E3,B3-SUM(F$3:F3)) | =MIN(E4,B4-SUM(F$4:F4)) | Formula |
Feb | 600 | Formula | Formula | Formula | Formula |
Mar | 400 | Formula | Formula | Formula | Formula |
Step-by-Step Guide for Formulas
1. Calculate Remaining Months for Each Goal:
=DATEDIF(TODAY(),C2,"m")
2. Monthly Savings Needed:
=B2/D2
3. Allocate Monthly Disposable Income:
=MIN($B$2,$B$2-SUM($F$2:F2))
For "Goal 2 Allocation":
=MIN($B$3,$B$3-SUM($G$2:G2))
4. Calculate Leftover After Savings:
=B7-SUM(C7:E7)
5. Flagging Insufficient Funds:
=IF(SUM(C7:E7)>B7, "Insufficient Funds", "")
4. Automating Adjustments:
As your savings targets, dates, or disposable income changes, all the formulas will update automatically to reflect the new situation. If you increase your disposable income for a month or reduce a target, the plan will automatically adjust.
5. Flagging When Goals are Met:
Once a savings goal is met, any extra income should go to the next goal. You can easily track this by summing the contributions made to each goal and comparing them to the target amount:
=IF(SUM($C$7:C7)>=B2, "Goal Met", "")
This formula will flag the goal as "Goal Met" when the total contributions reach or exceed the target.
Example Workflow:
Let’s assume in January, you have £500 disposable income:
In February, you have £600:
When Goal 1 is fully funded, the remaining disposable income automatically goes toward the next goal in line.
Conclusion:
This solution allows you to:
By setting up this structure, you’ll be able to effectively plan for savings across multiple goals, ensuring your funds are allocated efficiently while keeping you on track to hit your deadlines. I cannot open the Excel file for personal security reasons. The text, steps and the formulas were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Sep 29 2024 10:59 AM
Thank you so much for this response! Super detailed and gives me a template to work from.
I started trying to implement this template using just your figures and exact guidance on cell addresses etc, but can't seem to get past the first hurdle. I don't know what I am doing wrong and without understanding the formulas perfectly and what your intentions are, it's difficult to alter the formulas to try and get the correct results.
The image you gave with some of the formulas written out (starting in A7) doesn't agree with what you have typed further below? You are saying to refer to F$2:F2, but there isn't anything in this cell range? I haven't read further on your post to things such as 'Flagging insufficient funds' etc as I think I need to get this first bit right!
I'm so sorry but I am super confused! I appreciate your help so much; could you please view the attached and see where I am going wrong please? I really appreciate your time.
Thank you.
Sep 30 2024 04:15 AM
Let's break it down step by step, so you can understand exactly how each piece fits together. I'll correct any inconsistencies from the original explanation and align everything properly.
Step-by-Step Walkthrough of Setting Up the Savings Plan
1. Savings Goals Section
This is where you'll set up each of your savings goals (e.g., vacation, car repair). Each goal has a target amount, a target date, and an automatically calculated number of months to save.
For this, assume that this part of the spreadsheet starts in A1.
A | B | C | D | E |
Goal | Target Amount (£) | Target Date | Months to Save | Monthly Required (£) |
1) Vacation | 3000 | 15/06/2025 | =DATEDIF(TODAY(), C2, "m") | =B2/D2 |
2) Car Repair | 1000 | 15/10/2025 | =DATEDIF(TODAY(), C3, "m") | =B3/D3 |
3) Wedding | 5000 | 15/12/2025 | =DATEDIF(TODAY(), C4, "m") | =B4/D4 |
Breakdown of Formulas:
2. Monthly Budget Section
Now we set up the section where you enter your monthly disposable income and calculate how much is allocated to each goal.
Let's assume this section starts in A7.
A | B | C | D | E | F |
Month | Disposable Income (£) | Vacation Allocation | Car Repair Allocation | Wedding Allocation | Leftover |
Jan | 500 | Formula (see below) | Formula (see below) | Formula (see below) | Formula |
Feb | 600 | Formula (see below) | Formula (see below) | Formula (see below) | Formula |
Mar | 400 | Formula (see below) | Formula (see below) | Formula (see below) | Formula |
Breakdown of Formulas:
Formula for Goal 1 (Vacation) Allocation:
For Vacation Allocation in C7 (January):
=MIN($E$2, $B7 - SUM($C$6:C6))
Explanation:
You can drag this formula down for each month (C8, C9, etc.).
Formula for Goal 2 (Car Repair) Allocation:
For Car Repair Allocation in D7 (January):
=MIN($E$3, $B7 - SUM($C$7))
Explanation:
Drag this formula down similarly.
Formula for Goal 3 (Wedding) Allocation:
For Wedding Allocation in E7 (January):
=MIN($E$4, $B7 - SUM($C$7:D7))
Explanation:
Drag this formula down for each month.
Formula for Leftover Income:
In F7, to calculate any leftover income after the savings allocations:
=B7 - SUM(C7:E7)
This formula subtracts the total savings allocations from the disposable income for the month.
3. Flagging Insufficient Funds
If the total disposable income is not enough to meet the required savings allocations, you can add a warning system.
For example, in cell G7:
=IF(SUM(C7:E7) > B7, "Insufficient Funds", "")
This formula will show "Insufficient Funds" if the savings goals exceed the disposable income for the month.
4. Tracking Goal Completion
To check if a goal is met, you can add a formula that flags when the total contributions reach the target amount.
For example, in H2 (next to the Vacation goal):
=IF(SUM($C$7:C7) >= $B$2, "Goal Met", "")
This will show "Goal Met" when the total contributions for the vacation meet or exceed the target amount (£3000).
Summary of Key Steps:
Once you get this framework in place, it will dynamically adjust as you change values, so you can experiment with different income levels, target dates, and amounts. The text, steps and the formulas were created with the help of AI.