+ Reply to Thread
Results 1 to 45 of 45

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

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    Im really new to Excel and I do not know how to start on this assignment.. Can i get some guidance on this? Thank you so much!!
    Last edited by lwtoh001; 08-24-2020 at 09:27 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2304
    Posts
    13,184

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

    Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

    We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

    If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

  3. #3
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

    Re: 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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  5. #5
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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?

  6. #6
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  7. #7
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    I have completed 5 years of savings.
    Last edited by lwtoh001; 08-26-2020 at 04:41 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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?

  9. #9
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    What do you mean by a test?

  10. #10
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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

  11. #11
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  12. #12
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  13. #13
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  14. #14
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    I tried the if function for the stock balance until the 5th year. However, i do not understand how to use the function after the 5th year since the formulas
    are different.
    Last edited by lwtoh001; 08-23-2020 at 03:57 AM.

  15. #15
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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)

  16. #16
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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.

  17. #17
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    OK...look at something like this in cell D11...

    =IF($A11<$B$7,($C11+D10)*(1+$B$5),D10*(1+$B$5))

    Try to work out what it is doing and why some cells are locked (eg. $B$5) and why sometimes only columns are locked.

    Drag this down to all cells from the bottom right corner of cell D11 and look at the formula it creates in each of the cells in the column.

    Drag it across to the E column and modify it accordingly and drag that one down when you have it right.

    You also need to format your cells into something more appropriate than just a 'general' format. Try Currency or Number with 2 decimal points (if you don't want the $ sign)
    Last edited by Croweater; 08-19-2020 at 09:49 AM.

  18. #18
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    Thank you!! I understand how to use it on column D.
    Last edited by lwtoh001; 08-26-2020 at 04:41 AM.

  19. #19
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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?

  20. #20
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  21. #21
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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!!

  22. #22
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  23. #23
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    this is for year 2020 for stocks balance. Im not sure if im right...
    Last edited by lwtoh001; 08-23-2020 at 03:57 AM.

  24. #24
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  25. #25
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    Quote Originally Posted by lwtoh001 View Post
    =IF(A10<B7,B2*(1+B6),B2+(D10*(1+B6))) this is for year 2020 for stocks balance. Im not sure if im right...
    Look at the numbers and get your calculator out. Does your spreadsheet give the right answer? If not your formula is wrong.

    B2 is the opening balance for your BANK not STOCKS...correct?
    Last edited by Croweater; 08-20-2020 at 04:22 AM.

  26. #26
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    put 0 in the formula so that i can understand better.
    Last edited by lwtoh001; 08-26-2020 at 04:42 AM.

  27. #27
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

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

  28. #28
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    This is my updated copy
    Last edited by lwtoh001; 08-23-2020 at 11:48 PM.

  29. #29
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    OK. You are very nearly there.

    I have noticed a problem with how you have bracketed your formula.

    Have a look at the formula in D11...

    =IF($A11<$B$7,(D10+(C11*(1+$B$5))),D10*(1+$B$5))

    What year in school are you? Do you remember BODMAS or in the US I think they use PEMDAS?

    This means that Multiplication is performed BEFORE addition (unless brackets dictate otherwise..correct?

    so this...(D10+(C11*(1+$B$5))) does not multiply D10 by your interest whereas it should.
    There is a similar issue in the Stock column too. Don't forget, get it right in D11/E11 and drag down.

    Get this right and you are just about there! Good job!
    Last edited by Croweater; 08-20-2020 at 05:38 AM.

  30. #30
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

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

  31. #31
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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?

  32. #32
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  33. #33
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  34. #34
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    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.

  35. #35
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  36. #36
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  37. #37
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    omg I THINK THIS WORKS!!!!
    Last edited by lwtoh001; 08-24-2020 at 09:25 PM.

  38. #38
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  39. #39
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  40. #40
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    Now that you have done this yourself, I can show you my spreadsheets. One is done using my own (simple) formula, the other using Excel's built in formula.
    Make sure you submit your own work though not mine.

    You can see that if you get paid fortnightly, it will make about $2,000 difference over 20 years! You can play around with interest rates and number of pay periods over the year to see the difference.
    (You'll also see that when doing financial forecasts, I tend not to go down to the last cent, although Excel will calculate it to the last cent. The downside is that you may think that some of your columns don't add up exactly due to rounding errors)

    If you make it just 1 payment period for the year, the two come out exactly the same.
    Also you'll see I have used a 'conditional format' of cells so that when you change the year you start investing in Stocks, the cell format changes automatically.

    Hope your assignment has taught you something about Excel!
    Last edited by Croweater; 08-24-2020 at 04:05 AM.

  41. #41
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  42. #42
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

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

  43. #43
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    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.

  44. #44
    Registered User
    Join Date
    08-19-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    27

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

    Hi just a question. Should i use goal-seek for this question?
    Last edited by lwtoh001; 08-23-2020 at 11:47 PM.

  45. #45
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,119

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

    You could. I'm not sure how they have changed this function in later versions of Excel, but in my (old) version it's pretty useless IMO.

    I think the key word in this question is 'realistically'. It's no use plugging in $45K at year 10 and it coming up with an interest rate of 30% as that is not realistic.

    It's just as easy to change some values yourself to what you consider to be realistic values to come up with your savings goals, I feel, and you have designed your spreadsheet to do exactly that.

    What would be really neat is to put your savings goals in a column for each year and apply some conditional formatting to show if you have reached those goals.
    Last edited by Croweater; 08-23-2020 at 09:27 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Depreciation Calculation for Multiple Investments
    By asifmian55 in forum Excel General
    Replies: 1
    Last Post: 01-18-2017, 12:31 PM
  2. Savings Assignment
    By cheggie in forum Excel General
    Replies: 3
    Last Post: 09-18-2007, 08:41 AM
  3. Using FV for investments
    By DANASHOKES in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2007, 01:28 AM
  4. How do I set up an Investments Trading and Cash Template?
    By Bambo Excel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-10-2006, 02:45 AM
  5. [SOLVED] need help charting a time progression for investments
    By Elwood in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-10-2006, 04:50 PM
  6. INVESTMENTS OPPORTUNITES: Get 10.5% or 50% Monthly returns
    By Israel Fagbemi -- Top Rank Business Associates Gro in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 11-06-2005, 05:55 AM
  7. INVESTMENTS OPPORTUNITES: Get 10.5% or 50% Monthly returns
    By Israel Fagbemi -- Top Rank Business Associates Gro in forum Excel General
    Replies: 0
    Last Post: 11-06-2005, 05:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1