Forum Discussion

Andrew_Hinson's avatar
Andrew_Hinson
Brass Contributor
Sep 27, 2024

Please help! Savings for 2025

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.

 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Andrew_Hinson 

    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:

    • Savings Goals Section (Top of the Sheet)
      • Savings Goal (e.g., Vacation, Car Repair, etc.)
      • Target Amount (e.g., £3000 for a vacation)
      • Target Date (e.g., 15/06/2025)
      • Months Remaining (calculated as the number of months between the current date and the target date)
      • Monthly Required Saving (Target Amount ÷ Months Remaining)
      • Columns:
    • Monthly Disposable Income Section (Below the savings goals)
      • Month (January, February, etc.)
      • Disposable Income Available (e.g., £500 for January)
      • Allocated to Goals (Amount allocated toward your savings goals)
      • Leftover After Savings (Remaining after savings allocation)
      • Columns:

    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:

      • In the "Months to Save" column, we use the DATEDIF() function:

    =DATEDIF(TODAY(),C2,"m")

      • This calculates how many months you have until the target date.

    2. Monthly Savings Needed:

      • In the "Monthly Required (£)" column, we divide the target amount by the remaining months:

    =B2/D2

      • This tells you how much you need to save each month to meet the goal.

    3. Allocate Monthly Disposable Income:

      • For each month, allocate the disposable income first to the goal with the earliest target date, then to the next goal once the first is fully funded.
      • In the "Goal 1 Allocation" for January:

    =MIN($B$2,$B$2-SUM($F$2:F2))

      • This ensures that you allocate funds only until the savings goal is met.

    For "Goal 2 Allocation":

    =MIN($B$3,$B$3-SUM($G$2:G2))

      • Similar logic is applied, ensuring allocation only happens if the earlier goals have been fully met.

    4. Calculate Leftover After Savings:

      • Subtract the sum of the allocations from the disposable income for each month:

    =B7-SUM(C7:E7)

      • This shows how much you have left after saving for your goals.

    5. Flagging Insufficient Funds:

      • Add a condition to flag if you don’t have enough disposable income to meet the goals.
      • For example, if the total required savings for a month exceeds the disposable income, you can use conditional formatting to highlight it, or create a formula that shows a warning:

    =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:

    • £200 goes towards Goal 1 (Vacation).
    • The remaining £300 is allocated towards Goal 2 (Car Repair).

    In February, you have £600:

    • The same logic applies: more funds go towards Goal 1, and whatever is left goes to Goal 2 and Goal 3 as needed.

    When Goal 1 is fully funded, the remaining disposable income automatically goes toward the next goal in line.

    Conclusion:

    This solution allows you to:

    • Dynamically allocate your disposable income to multiple savings goals.
    • Automatically adjust the savings plan as your disposable income or savings goals change.
    • Track if there’s insufficient income to meet your targets in time, and adjust accordingly.

    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.

    • Andrew_Hinson's avatar
      Andrew_Hinson
      Brass Contributor

      NikolinoDE 

      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.

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        Andrew_Hinson 

        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:

        • Target Amount (£): This is the total amount you need for the savings goal (you enter this manually).
        • Target Date: When you want to complete saving for this goal.
        • Months to Save: This is calculated using the DATEDIF() formula. It gives you the number of months left until the target date, starting from the current date.
          • Formula: =DATEDIF(TODAY(), C2, "m") (change C2 for each row).
        • Monthly Required (£): The amount of money you need to save each month to reach your goal by the target date.
          • Formula: =B2/D2 (again, adjust for each row).

        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:

        • Disposable Income (£): This is the amount of money you have available to allocate to your savings goals for that month. You enter this manually.
        • Goal Allocations (Vacation, Car Repair, Wedding): Here, we allocate your disposable income based on the target dates of your goals. The goal with the earliest target date will get funded first, and after that is fully funded, any extra money will flow to the next goal.

        Formula for Goal 1 (Vacation) Allocation:

        For Vacation Allocation in C7 (January):

        =MIN($E$2, $B7 - SUM($C$6:C6))

        Explanation:

        • $E$2: This refers to the "Monthly Required (£)" for the Vacation goal (from row 2).
        • $B7: This is your disposable income for January (cell B7).
        • SUM($C$6:C6): This ensures that we account for any savings already made in previous months for the goal.

        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:

        • $E$3: This refers to the "Monthly Required (£)" for Car Repair.
        • $B7 - SUM($C$7): This subtracts the amount already allocated to Vacation for that month.

        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:

        • $E$4: This is the "Monthly Required (£)" for Wedding.
        • $B7 - SUM($C$7:D7): This subtracts the amounts allocated to Vacation and Car Repair for the current month.

        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:

        • Set up your savings goals: Create a structured table that includes target amounts, dates, and the number of months to save.
        • Allocate disposable income: Use the MIN() and SUM() functions to dynamically allocate money to savings goals based on target dates.
        • Flag issues: Add flags to warn when there’s not enough income to meet goals.

        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.

Share

Resources