Excel 2013: The Pv (Present Value) Function And Winning The Lottery Video

Winning the Lottery

Demonstrates how to use the PV (Present Value) function in Excel 2013 to decide which is better when somebody wins the lottery: the lump sum or the regular payments.

this video we’re gonna take a look at another example of using the present value function in excel in this case what we’re gonna do is we’re gonna look at the lottery problem whenever somebody wins a lottery if you want a twenty million dollar lottery for example you don’t actually get twenty million dollars what you get is you get that twenty million dollars paid off over a period of time so you usually have the option of either taking that amount over a period of time or you can take a lump sum today which is considerably smaller and so the problem we’re trying to solve here is how do you decide whether you should take the lump sum or whether you should take the million dollars a year over 20 years and it all depends on what you think the interest rate is that you could earn on that lump sum so here’s what we’re trying to figure out we’re trying to figure out if we took the lump sum and we put it in a savings account and we earn 6% on it for 20 years and at the beginning of every year we withdrew a million just like we would if we were taking the other payment choice in the lottery would we have enough money to withdraw a million dollars per year while still earning 6% on that remaining value balance and if the amount of the lump sum allows you to withdraw a million dollars a year and still have some left over at the end then you should take the lump sum if you run out before 20 years then you should probably take the 1 million dollars a year so this is this is what we’re trying to figure out here we’re trying to figure out the beginning balance a is what the the lump sum payment should be and we don’t know that but we know we’re gonna withdraw 1 million every year and we only do it once a year so this is one of those rare problems where we’re not doing monthly calculations so the number of periods per year is going to be 1 the number of years is going to be 20 the annual interest rate I think is going to be 6% and the ending balance is zero and that’s formatted wrong that should be formatted like this and the number of periods we can do a formula here it’s going to be the periods per year times the number of years and the rate per period is going to be and again in this case really don’t need those formulas but if these numbers would change then we wouldn’t have a problem changing the formula you just go in here and change the numbers and our balance would automatically be updated and we’re going to do these payments at the beginning of every year okay so that’s the information and then what we need to do over here is we need to use the present value function non financial here scroll down to the letter P and there’s PV and we need to tell what’s the rate gonna be well the rate is going to be the rate per period which in this case happens to be the annual rate but that’s usually not the case the number of periods is here and again that’s the number of years but that’s usually not the case payment is always a negative number and that’s how much we’re gonna withdraw every year future value we want it to be zero when we’re done and the type is gonna be this and if we click on OK it tells us that if we had twelve million one hundred fifty eight thousand dollars and one hundred sixteen dollars and forty nine cents and we withdrew a million right away and kept that money invested at 6% and then withdrew a million next year and still kept investing the money at the end of twenty years we would have exactly zero left that the amount to withdraw at the beginning that twentieth year would be exactly a million dollars would withdraw the million and we have a balance of zero so if 6% is the interest rate you think you can earn and the lottery offers you more than twelve million one hundred fifty eight thousand one hundred sixteen dollars then you should take that on the other hand if they offer you less than that then you’d probably better off taking the 1 million dollars a year.


Comments on this entry are closed.

Previous post:

Next post: