Spreadsheet Modelling Application Practice

[n_mayo]

Hi!

I'm new to excel and am struggling to figure out how to do this question. I would like to seek some advice and direction on how to approach this:

Your annual income is now $32000, and this is expected to increase at about 3% per year. Your bank balance is zero and the plan is to start growing it by saving 10% of your income. Bank interest is now 1% per annum, after 5 years you should have enough money and knowledge to want to invest your money into bonds and stocks, to get return rates of at least 3.5%. Construct a spreadsheet model of your savings for the next 20 years. What if your income grows at 2% and savings at 8% of income?

So far I have come up with a table consisting of all the information but am also not too sure if the calculation for interest and returns are based on savings?

Hopefully someone can share some advice!

 

[alansidman]

Please share what you have up to this point. Use XL2BB to upload your sample data.

 

[joeu2004]

I'm [...] struggling to figure out how to do this question. I would like to seek some advice and direction on how to approach this:
[....]
[...] not too sure if the calculation for interest and returns are based on savings?

Since this is obviously an assignment of some sort, it would be unethical for us to provide turnkey solutions. And you are wise to only ask for "advice". So no more details are necessary, IMHO.

To answer your question (``not too sure if the calculation for interest and returns are based on savings?``), the interest rate applies to bank savings, which grows by ``10% of your income`` each year. And your income grows by ``3% per year``.

That is true for at least the first 5 years.

After that for the remaning 15 years, the (average) return rate applies to your portfolio, which additionally grows by ``10% of your income`` annually (and your income grows by ``3% per year``).

But I think that is subject to interpretation.

Another interpretation might be: you invest the balance of the savings after 5 years into a portfolio of stocks and bonds, which grows by 3.5% annually over the next 15 years. And you also continue to add 10% of your growing income to savings, which resets to zero after 5 years and which grows by 1% annually over the next 15 years.

You might ask for clarification. Alternatively, you might explain the interpretation that you choose together with your implementation. Or offer solutons for both (and any other) interpretations, which I think makes for an interesting comparison.

In the real world, it is advisable to keep some cash in your portfolio of securities and other investments. But to that end, I would say that after 5 years of savings-only, I would invest 10% of the growing income in a portfolio that includes some percentage of cash (savings) as well as additional stocks and bonds. The cash earns 1% interest, and the securities earn 3.5%.

(That is a "third" interpretation that seems to go beyond the assignment.)