Excel 2010: Buy versus lease calculation

Shows how to analyze the total cost of leasing versus buying, using Excel 2010. Follow us on twitter:

Hello and welcome in this video we’ll take a look at how to compute a lease or purchase decision so let’s say you have a computer that you want to buy and you are trying to decide whether to purchase the computer or release it and let’s put some numbers here let’s say the cost of the computer is $2,000 and let’s say if you want to lease it the annual lease payment

Is $700 if you were to purchase it you would basically pay the entire cost upfront of $2000 if you were to lease it you would instead pay $700 as your first installment and $700 in the at the end of the first year second year and third year and now we want to compare the two alternatives so if you look at the total cash flow for purchase it is simply two thousand

Dollars whereas if you look at the total cash flow for lease you need to compute the net present value of these cash flows and one way to do that is in excel to first add up the zeroth period or initial cash flow plus and now you can use npv function npv of the rate the rate is 8% and the set of values are the values in c 8 through c 10 and you can close the

Parentheses press enter so this here is the net present value of leasing so as you can see here clearly purchasing is a smarter option because overall the lease is way too expensive now you can also play around a little bit with this and let’s say what if your lease was 580 dollars instead of 700 and what if the annual interest rate that you can borrow at your

Bank is 12% instead of 10% now suddenly the purchase of option appears more expensive and the lease option appears less expensive so just to give some insight into how we are computing this net present value i’m just going to do the net present value computation in a slightly different way so what i’m going to do is in pv calculations i’m just going to calculate

The present value of this cash flow this this and this cash flows over here and then i’m going to add them up over here so the net present value is another way to compute this is to compute the individual present values all right let me just say pv here equals this amount divided by 1 plus the interest rate raised to the power of the year in this case a 7 and

I want to go back here and modify the b 4 formula i want the b 4 formula to remain as b 4 as you drag this formula all the way down but i want c 7 to become c8 c9 and c 10 and i want a 7 to become a 8 a 9 and a 10 so i leave these two alone in a press enter and now if you just drag the fill handle down you will get the present value of 580 dollars one year from

Now today’s present value of phi 80 dollars that you will pay out one year from now is five seventeen dollars and 86 cents and the present value of 580 two years from now is 462 and the present value of 580 three years from now is four hundred twelve dollars and the sum of all of those is your present value and you can see that this present value is identical to

This value that we computed using the npv formula of excel so based on this we can also include a small f function to give our decision if the purchase price is less than the least cash flows then you say purchase if not you say lease press enter so as you can see given these these particular numbers you want to lease but if the numbers change a little bit let’s

Say it is six twenty dollars instead of five eighty then you want to do a purchase option or if you wanted this to be 580 but if suddenly your interest rate is lesser then again you want to purchase so that’s how you would make a typical buy versus lease calculation i hope you like this thanks for watching

Transcribed from video
Excel 2010: Buy versus lease calculation By Codible