Hi i need some help for my assignment on savings/investments!!

I am prepared to help you, but as has been said above...not do it for you.

Why don't you start by putting all your constants (given in the assignment) in cells in your spreadsheet (Things such as your income in your first year, the annual increase etc.

Then, Create a table of (for example) Year, Income, Savings(from your income), Bank Balance and Stock Balance.

Then you fill in your table (using your constants) in your formula for each column in your table.

You can do things like... see how much quicker your balance will grow if you save 20% of your income instead of 10%, or if you can get some better interest than 1% form your bank.
By the way if you can get better interest than that from a savings account, be sure to let me know! ;-)

Start off with a very basic table and formulae, then you can build on it as you go.

See attached to start yourself off and see how you go.

Im sorry, Im just here to learn. I have rephrased the title of my post.

Hi, just a quick question. For the first 5 years, the plan is to save 10% of my income. So is it safe to say that this person will spend 90% of his savings? The bank balance for 2020(First year) will therefore be 10% of annual income + Interest gained from bank?

Not being pedantic but I would not say this person would 'spend 90% of savings' but 'spend 90% of income'. Subtle difference.

Savings will grow at 1% compounded yearly. That is after the first year 10% of income + 1%, but in the following year you will (in addition) have 1% of your existing balance from the previous year.

OK..I think you are going well. Your question is open to interpretation.

The way I took it to be was that instead of withdrawing your whole savings from your bank and putting it into stocks you start investing your annual 10% into stocks. It's up to you I guess.

One thing though. Your formula for your ongoing bank and stocks balance must include a test to see if the year is less than/greater than the year you start to invest in stocks.

If you don't do this, your formula will not work if you decide to start investing in stocks earlier or later. See my point?

What do you mean by a test?

Btw, i decided to invest annual 10% into stocks like what you said.
Its better because i can get the bank interest as well as stock returns

By 'a test' I mean check whether the year is greater than or less than the year which you have said you will make the switch to stocks.

Do this using an IF statement in your cell formula. If you don't do this your ongoing balances will be wrong if you change the year in B7.

Im not familiar with "if" function
The If function should be included in year 2020 all the way to 2039?

Yes it should. Your formula should be dragged all the way to 2039

The way an IF works is; =IF (logical_test, [value_if_true], [value_if_false]).

eg. =IF(C10>C1, 1,0). This means if the value in C10 is > than the value in C1 then the value of this cell would be 1, otherwise it would be 0.

So your ongoing balances should be checking against the year in B7 in your example.

You will need to calculate your balance differently if the current year is past/before the year in B7.

Try this for yourself and do some research on the net. I will be callling it a night from this post. I can give you some more help in the morning if you need it.

Keep at it.

How can i create an "If function" Such that the bank balance and stock balance will change according to the year? I have done all the manual calculation. Thats why after the 5th year, my calculations for bank balance and stock balance are different.
Basically i have to create an if function such that when the year is inputed, the savings will be invested into stocks and the bank balance will remove annual savings. (There will still be bank interest)

Oh totally forgotten about the assumption that he can only invest after the 5th year. So can i use two "if" functions for this? One if function for 1st-5th year and the other if function for 6th years onwards.

Referring back to the question, I will only have enough money to invest in stocks/bonds after 5 years. Can i just use the if function from year 2025-2039?

OK...I think you are getting there. The answer to your last question is "No". because if you change your year to a year before 2025 if will not work.

Your formula will need to be different for the first year (2020) but for 2021 the formula will be the same (i.e. for 2021 - 2039) in each column.
This is what I have in D10
=IF($A10<$B$7,(C10+$B$2)*(1+$B$5),$B$2*(1+$B$5))

...and this for D11
=IF($A11<$B$7,($C11+D10)*(1+$B$5),D10*(1+$B$5))

Very similar but the first is based around your initial balance but the rest of the column is based around your previous years balance.
This means the D11, being a generic formula can be dragged down the rest of the columns.

You need to do a similar thing for your Stocks column. This will be easier because there is always an opening balance of zero on this account.

So for my stock balance, i need to have one based on initial and the other one based on previous? I will try it out now thanks!!

To calculate stock balance, do i need to include savings opening balance?

No. From your assignment it says you will only venture into stock trading once you have experience. From that I take it to be an opening balance of zero.

However it says your bank balance is 'practically zero'...which indicates it may be a low number but not exactly zero, which is why I had that as a cell value, so you can change it.

Put your latest spreadsheet up and I will have a look when I get the chance...

Once you have the formula right, I have just a couple of other suggestions to finish off your assignment.

I understand what you are trying to say.
But I have a question...

Compound interest formula is P(1+r)^n right?
Here is an example : Assuming we deposit 600 every year.
Principal: 2000
Rate: 2.5%
Number of years: 2

Using excel FV = we get 3316.25
This is how we normally calculate :
year 1: 600+2000(1+2.5%) = 2650
Year 2: 600+ 2650(1+2.5%) = 3316.25
The answers are the same right, using FV and manual calculation.
But, if we were to put brackets in front, (600+2000)(1+2.5%) = 2665
the answers are already different. I understand that if i put a bracket in front, i will get the interest. But if i drag the formula down,
all the equations will be messed up wont it?

That is why for my stocks balance, i could not get the interest i want because the previous cell is 0.
Savings + O*(.....) = Savings.

Sorry I may have misled you with the brackets.

What I was trying to say is that you are multiplying your yearly savings amount by the interest...not your savings balance.

However you are right, the FV function in excel will give you more accurate calculations than my manual ones.

I just multiplied my saving balance by the interest. =IF(A15<$B$7,0,E14+C15*(1+$B$6)) Yes im able to get my stock interest this way. But
even if i drag the formula down, all values will be different again.

If stock balance is not 0, i would have completed this question

Is it possible to use two If functions in the same cell? IF year chosen is selected, that year will automatically + stock interest rate.

I've dragged it down and it worked perfectly!!

Well done!

You could use Excel's financial functions and see how close they are to yours.

Seems like you are learning how to use Excel, which is what this exercise is all about.

Thanks for helping!!! I really appreciate it!
I’ll take a look at the spreadsheets when i’m free!

Do you specialise in excel? You’re like a pro hahaha

You're very welcome. No, I don't 'specialise in Excel'. Believe me, there are a lot of people on this forum who are better at it than me!

You'll be surprised at how much you can learn in a lot of different areas as you progress through life!

All the very best. I've enjoyed helping you out.