Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Please help me do this in MS Excel

  • 12-05-2014 10:41am
    #1
    BenEadir
    Registered Users, Registered Users 2 Posts: 4,539 ✭✭✭


    Hi,

    I want to express the present value of a a future income stream in a MS Excel model I'm working on and I'm stumped as to how to express it using a formula rather than a series of manually calculated numbers.

    The calculation I need to make is as follows:-

    I'm starting in year 1 with an income of €100,000 and the assumption is that over time this income will decline by 5% per annum so in year 2 it will be €100,000 X 95% = €95,000, in year 3 it will be €95,000 X 95% = €90,250 and so on for however many years it takes until the income is effectively €0. (I know in theory it will never get to absolute zero)

    Is there a formula in MS Excel which will allow me to input the principal amount (in this case €100,000) and the perpetual decline in percentage terms (in this case 5%) and return a total figure?

    Cheers,

    Ben


Comments

  • #2
    hidinginthebush
    Closed Accounts Posts: 2,679 ✭✭✭hidinginthebush


    =[cell]*0.95 should do it for you!


  • #3
    BenEadir
    Registered Users, Registered Users 2 Posts: 4,539 ✭✭✭BenEadir


    hidinginthebush wrote: »
    =[cell]*0.95 should do it for you!

    No, that just gives me the value for the next year. I'm looking for a single formula which will give me the value for all future years.

    Ben


  • #4
    ads20101
    Registered Users, Registered Users 2 Posts: 1,452 ✭✭✭ads20101


    BenThere wrote: »
    No, that just gives me the value for the next year. I'm looking for a single formula which will give me the value for all future years.

    Ben

    Drag the formula across


  • #5
    hidinginthebush
    Closed Accounts Posts: 2,679 ✭✭✭hidinginthebush


    If you start off 100000 in cell A1, then input the formula into cell A2, you'll get the result in A2.

    Then, click in A2 and hover the cursor over the bottom right of the cell in A2 until it turns from a cross to a plus sign, then left click and drag it down and it'll do it for all subsequent years


  • #6
    BenEadir
    Registered Users, Registered Users 2 Posts: 4,539 ✭✭✭BenEadir


    Thanks for the suggestion guys.

    I figured out that the formula is as follows:-

    €100,000 X .95 / (1 -.95) = €1,900,000

    Thanks for the inspiration!!!

    Ben


  • Advertisement
  • #7
    Yakuza
    Registered Users, Registered Users 2 Posts: 5,141 ✭✭✭Yakuza


    The formula for a geometric series is a/(1-r), or in this case, 100000/(1-0.95) or 2,000,000.
    More info here: http://en.wikipedia.org/wiki/Geometric_progression


  • #8
    BenEadir
    Registered Users, Registered Users 2 Posts: 4,539 ✭✭✭BenEadir


    Yakuza wrote: »
    The formula for a geometric series is a/(1-r), or in this case, 100000/(1-0.95) or 2,000,000.
    More info here: http://en.wikipedia.org/wiki/Geometric_progression

    Thanks Yakuza,

    Man do I wish I'd taken honours maths back in the day :o

    Ben


Advertisement