+ Reply to Thread
Results 1 to 11 of 11

Calculating investment amount...

  1. #1
    Registered User
    Join Date
    10-03-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Calculating investment amount...

    Hi all,

    New to this forum so please take it easy on me!

    Im looking for a formula that calculates an investment amount when I know profit (excl original investment), required return and the investment period.

    I'm sure its quite simple but cant seem to find one that works!

    Thanks

    Nails

  2. #2
    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: Calculating investment amount...

    Welcome to the board.

    A
    B
    C
    1
    Profit
    $10,000.00
    2
    numPeriods
    10
    3
    Return
    5%
    4
    Investment
    $15,900.91
    B4: =B1/((1+B3)^B2 - 1)
    5
    6
    Check:
    7
    Year
    Balance
    8
    0
    $15,900.91
    9
    1
    $16,695.96
    10
    2
    $17,530.76
    11
    3
    $18,407.30
    12
    4
    $19,327.66
    13
    5
    $20,294.04
    14
    6
    $21,308.75
    15
    7
    $22,374.18
    16
    8
    $23,492.89
    17
    9
    $24,667.54
    18
    10
    $25,900.91
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-03-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating investment amount...

    Thanks shg - much appreciated!!

  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: Calculating investment amount...

    You're welcome.

  5. #5
    Registered User
    Join Date
    10-03-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating investment amount...

    Ok - have another layer of detail I need to add.

    I need to invest a known $ amount in period 2. Therefore, the "balancing" investment amount is invested at T0.

    Any ideas on a suitable formula?

  6. #6
    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: Calculating investment amount...

    I understand those words individually, but not the question.

  7. #7
    Registered User
    Join Date
    10-03-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating investment amount...

    Ok

    Does this make it any clearer?

    Profit 10,000
    No. periods 5
    Return 10%
    Investment (known amount) 5,000
    Investment (known amount) period paid 2
    Investment - Flexible (balancing amount) ??
    Investment - Flexible period paid 0

  8. #8
    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: Calculating investment amount...

    No. Could you explain in full sentences?

  9. #9
    Registered User
    Join Date
    10-03-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Calculating investment amount...

    Trying to calculate an amount an investor can pay to buy a shareholding in a company given they need to make a certain level of return.

    The investors profit at the end of the period is say $10k
    The investment horizon is 5 years.
    The investors required return is 10%.
    The investor has to contribute a known fixed amount of (say $5k) in year 2.
    Im looking to know how much the investor will need to pay now to give him the projected return in 5 years whilst also allowing for the fact they will also need to invest $5k in year 2.

    Thanks!

  10. #10
    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: Calculating investment amount...

    Well ...

    You can figure out the profit $5K returns at 10% for four years, right? That tells you how much additional profit you need to make the original goal, for which you can use the solution in post #2.

  11. #11
    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: Calculating investment amount...

    Ensemble,

    A
    B
    C
    1
    Profit
    $10,000.00
    2
    numYears
    5
    3
    Return
    10%
    4
    Add'l Inv in Year
    2
    5
    Add'l Inv Amt
    $5,000.00
    6
    Profit on Add'l Inv
    $2,320.50
    B6: =((1+B3)^(B2-B4+1) - 1) * B5
    7
    Add'l Profit Req'd
    $7,679.50
    B7: =B1-B6
    8
    Initial Investment
    $12,578.83
    B8: =B7/((1+B3)^B2 - 1)
    9
    Total Investment
    $17,578.83
    B9: =B5+B8

+ 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. Calculating amount with dates?
    By Wolfin2010 in forum Excel General
    Replies: 5
    Last Post: 08-05-2015, 11:01 AM
  2. [SOLVED] Calculating amount of master items based on mixed amount of items
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-21-2014, 11:21 AM
  3. Help calculating stock investment returns vs S&P 500
    By mattmcg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2013, 02:12 PM
  4. Calculating Alpha & Beta For Investment
    By Vlad111 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2012, 06:25 PM
  5. SUMPRODUCT and calculating an amount
    By muchado77 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-24-2007, 12:09 AM
  6. Replies: 3
    Last Post: 02-12-2007, 06:28 PM
  7. calculating the future value of an investment in excel
    By John C. in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-24-2006, 05:00 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