+ Reply to Thread
Results 1 to 11 of 11

Financial problems

  1. #1
    Registered User
    Join Date
    06-14-2014
    Posts
    24

    Financial problems

    I have been taking an Excel class that I thought was for beginners, but in 5 weeks I have covered an entire book. Most functions I can work through but the financial problems are causing me major problems. I would appreciate any help in helping me understand which time value of money to use.

    First question:
    I would like to have $1,000,000 in ten years. I have $700,000 now in an account. I want to withdraw a $100,000 each year at the beginning of each year. What annual interest rate would we need to achieve our goal. I tried using Rate, but I am just not understanding this. This is the formula I used =RATE(10,100000,-700000,10000000,0) and the answer I got was 37%. I suck at math and this isn't helping me. Thank you for anyone who can assist me.

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Financial problems

    Two problems I do see are that you have said you would like to have $1M in ten years, but the equation has $10M, thus 37%. Also, you say the withdrawal is at the beginning of the year, so that last parameter should be a 1.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Financial problems

    deleted - posted in error
    Last edited by FDibbins; 06-14-2014 at 08:23 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Financial problems

    In fairness, Jackie was asked here to put all the questions in one thread. Having done that, she's going to be hard-pressed to find one title that covers them all.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Financial problems

    Did not read that thread, thx shg, I will leave it as is then. Thanks for the heads-up

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Financial problems

    Hi Jackie,

    I too have problems with those Time Value of Money problems. But Excel has the SOLVER. See if you can follow the attached example.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Registered User
    Join Date
    06-14-2014
    Posts
    24

    Smile Re: Financial problems

    Quote Originally Posted by MarvinP View Post
    Hi Jackie,

    I too have problems with those Time Value of Money problems. But Excel has the SOLVER. See if you can follow the attached example.
    Thank you.

  8. #8
    Registered User
    Join Date
    06-14-2014
    Posts
    24

    Re: Financial problems

    I appreciate all the help I can get. My professor sent me an email that helped me with a few. My biggest issue is trying to figure out which formula to use, I also started using the Insert Function tool and that helped some also.

  9. #9
    Registered User
    Join Date
    06-14-2014
    Posts
    24

    Re: Financial problems

    MarvinP you may have saved me with this little hint, thank you thank thank you!

  10. #10
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Financial problems

    Quote Originally Posted by Jackie66613 View Post
    I would like to have $1,000,000 in ten years. I have $700,000 now in an account. I want to withdraw a $100,000 each year at the beginning of each year. What annual interest rate would we need to achieve our goal. I tried using Rate, but I am just not understanding this. This is the formula I used =RATE(10,100000,-700000,10000000,0) and the answer I got was 37%.
    The correct formula to use is: =RATE(10,100000,-700000,1000000,1).

    In addition to correcting the typo in the FV parameter, the Type parameter is 1 to indicate that "payments" (withdrawals) are at the beginning of each period.

    See the explanation below.

    Quote Originally Posted by MarvinP View Post
    I too have problems with those Time Value of Money problems. But Excel has the SOLVER. See if you can follow the attached example.
    Me, too. However, first I try to use Goal Seek because it is easier to use. A personal preference; "to each his own". Just wanted to be sure Jackie was aware of the alternatives.

    In either case, beware of putting an initial value in the "by changing" cell. Both Solver and Goal Seek seem to use that in some way. It alters their results, sometimes beneficially, sometimes not in my experience.

    But using Solver or Goal Seek does not ensure a correct answer. The devil is in the details of the model -- the collection of dependent formulas that drive the "set objective" cell.

    IMHO, the following is the correct model for Jackie's specifications (columns E through G).


    A
    B C D E F G
    1
    MarvinP Model
    JoeU2004 Model
    2
    Year Balance Rate
    Year Balance Rate
    3
    1 700,000.00 16.66%


    700,000.00 18.72%
    4
    2 716,645.27

    1 712,311.02
    5
    3 736,064.25

    2 726,926.49
    6
    4 758,719.12

    3 744,277.75
    7
    5 785,149.12

    4 764,876.91
    8
    6 815,983.31

    5 789,331.92
    9
    7 851,955.59

    6 818,364.54
    10
    8 893,922.14

    7 852,831.64
    11
    9 942,881.84

    8 893,750.46
    12
    10 1,000,000.00

    9 942,328.67
    13




    10 1,000,000.00

    In MarvinP's model, the balance for Year1 in B3 is 700,000, and the balance for Year2 in B4 is =B3*(1+$C$3)-100000. The latter puts the withdrawal at the end of Year2, not at the beginning as Jackie specified.

    Moreover, the model comprises 9 years of growth, not 10 as Jackie specified.

    Arguably, that is subject to interpretation. Jackie wrote: "I would like to have $1,000,000 in ten years". If Jackie had written "in one year", that would be Year1 in MarvinP's model. In that case, there would be no withdrawal and no interest earned; the balance would simply be 700,000. Usually, when someone says "in one year", they mean "at the end of one year from now".

    In my model, the balance before Year1 in F3 ("now" as Jackie wrote) is 700,000. The ending balance for Year1 in F4 is =(F3-100000)*(1+$G$3). That is, the beginning balance is the previous year-end balance minus the withdraw, and the ending balance includes the interest earned on the beginning balance. Thus, the balance at the end of 10 years is 1,000,000.

    Note that the result in G3 is the same as the result of the RATE function above: about 18.72%.

    Jackie, I hope that is helpful.
    Last edited by joeu2004; 06-16-2014 at 11:59 AM. Reason: cosmetic

  11. #11
    Registered User
    Join Date
    06-14-2014
    Posts
    24

    Cool Re: Financial problems

    Thank you so much, this really breaks it down. These suckers are confusing!

+ 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. [SOLVED] Problems with sorting andf Pivot Table problems
    By Saturn in forum Excel General
    Replies: 4
    Last Post: 10-07-2012, 07:47 AM
  2. Replies: 1
    Last Post: 03-04-2012, 02:37 AM
  3. Financial Functions
    By mpsvictor in forum Excel General
    Replies: 1
    Last Post: 08-02-2010, 08:21 AM
  4. Financial Analysis
    By msrlg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-07-2009, 01:22 AM
  5. For financial guys...
    By efedrina in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-29-2007, 04:10 PM

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