Net balance Calculation
Bob and Carol are planning for the birth of their first child exactly four years from today. They are now ready to start their savings plan for the big event. The current hospital cost for having a healthy baby at the local hospital is $6500 after all insurance payments. Pre-natal care for the immediate 12-month period prior to having the baby amounts to $2000 out-of-pocket costs. Carol’s best friend is planning a baby shower, so only a crib, a baby carrier, and other miscellaneous items will be needed, which all cost $1,200 today. However, these items will be purchased and paid for the day of the child’s birth, and the items are expected to increase in costs by 10% each year over the next four years due to inflation.
Bob and Carol now have $500 in cash that they plan to put in the bank in order to cover the all the new costs. Also, Uncle Ted has promised to contribute $1000 at the end of year two, as a present to Bob and Carol for baby expenses.
Currently, Bob and Carol can earn 6% compounded annually on this money. In order to be able to pay cash for all these expenses on the day the baby is born, how much will Bob and Carol have to save, assuming the baby is born exactly four years from today
Questions:
- Draw the timeline that illustrates the timing of all the events of the situation described above.
- How much will Bob and Carol need to have in the bank on the day the baby is born in order to achieve all their goals?
- What amount needs to be saved at the end of each year in order for Bob and Carol to reach their financial goals?
Solution
Savings Management
A report on planning of savings, interest and inflation to cover costs over four years
Outline of events
Given events can be outlined in the following form, where first column signifies the end of which year is the data for and the second column signifies the relevant data:
End of Year # | Cash in |
0 | 500.00 |
1 | 0.00 |
2 | 1000.00 |
3 | 0.00 |
4 | 0.00 |
End of Year # | Costs to be incurred | Total Costs | |
0.00 | 0.00 | 0.00 | 0.00 |
1.00 | 0.00 | 0.00 | 0.00 |
2.00 | 0.00 | 0.00 | 0.00 |
3.00 | 2000.00 | 0.00 | 2000.00 |
4.00 | 1200.00 | 6500.00 | 7700.00 |
Above table shows the cash flowing in to the savings budget at the end of year 0 (currently) and end of year 2 (from Uncle Ted). Also the table #2 shows costs to be incurred at the end of year 3 (2000$ to be paid throughout year 4) and other charges at the time of delivery. This basically shows the minimum balance the savings account should have in order to cover costs safely for the next year.
Costs involved and inflation
Adjusting for inflation in the prices of all goods to be purchased as well as the rise in hospital charges by 10% every year we have adjusted the costs as below:
The green highlighted column shows the amount of balance the account should have at the end of each year
End of Year # | Costs incurred | Total Costs | Adjusted costs to inflation | Account Balance required | ||
0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
1.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
2.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
3.00 | 2000.00 | 0.00 | 2000.00 | 2662.00 | 0.00 | 2662.00 |
4.00 | 1200.00 | 6500.00 | 7700.00 | 1756.92 | 9516.65 | 11273.57 |
Thus total balance needed to be at the end of year #3 is 2662$
Similarly, total balance needed to be in the savings account at the end of year #4 is 11273.57
Savings
In order to calculate the savings to be done in every year from now on to cover for all costs at the end of year #3 as well as year #4, we need to take into consideration the following factors:
- Cash in
- Savings in previous year
- Expenses in previous year
- Interest on the net amount
Keeping all these into consideration, for the simplicity of the case we assume that Bob and Carol save an equal amount of money at the end of each year.
Here we calculate Net balance(NB) at the end of each year #x as follows:
NB(#x) = NB(#x-1) + Interest on NB(#x-1) + Cash in(#x) +Savings(#x-1) – Costs (#x)
Using the same we calculate the net balance at the end of year#3 and year#4 and equate the Net balance at the end of year#4 to ZERO to balance the costs with all the savings and interest in the previous years. We solve the following with Solver tool of Excel on variable (savings) to equalize net balance at the end of year#4 to be ZERO.
Following results are obtained:
End of Year # | Cash in | Savings | Interest earned | Costs incurred | Acct Balance needed | Net Balance | |
0 | 500.00 | 2820.92 | 0.00 | 0.00 | 0.00 | 0.00 | 500.00 |
1 | 0.00 | 2820.92 | 30.00 | 0.00 | 0.00 | 0.00 | 3350.92 |
2 | 1000.00 | 2820.92 | 201.06 | 0.00 | 0.00 | 0.00 | 7372.90 |
3 | 0.00 | 2820.92 | 442.37 | 2000.00 | 0.00 | 2662.00 | 7974.20 |
4 | 0.00 | 0.00 | 478.45 | 1200.00 | 6500.00 | 11273.57 | 0.00 |
Thus finding solutions using Solver tool of excel by varying the yellow highlighted cells to yield a final value of Net balance ZERO at the end of year#4, we come to know that Bob and Carol need to save 2820.92$ every year in order to meet the expenses by four years.
References
- Inflation: http://www.investopedia.com/terms/i/inflation.asp
- Compound Interest: http://www.investopedia.com/terms/c/compoundinterest.asp
- Solver: http://www.solver.com/solver-tutorial-using-solver
End of Year # | Cash in | Savings | Interest earned | Costs incurred | Adjusted costs to inflation | Acct Balance needed | Net Balance | ||
0 | 500.00 | 2820.92 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 500.00 |
1 | 0.00 | 2820.92 | 30.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 3350.92 |
2 | 1000.00 | 2820.92 | 201.06 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 7372.90 |
3 | 0.00 | 2820.92 | 442.37 | 2000.00 | 0.00 | 2662.00 | 0.00 | 2662.00 | 7974.20 |
4 | 0.00 | 0.00 | 478.45 | 1200.00 | 6500.00 | 1756.92 | 9516.65 | 11273.57 | 0.00 |