Equivalence For Repeated Cash Flows
In this Excel tutorial I'm going to show you how to solve for variety of things; how to find a present value given in A, how to find an A given a present value or even how to solve for n. In the Excel file that you see before you is some actual information that happened with my spouse. He wanted to buy a car and I was like, Hey I'm taking this engineering economy course, maybe I can help you out. He didn’t believe it would work and I would just like to state that I proved him wrong. Here's the information he gave me, he said he wanted about a $200 a month car payment, he didn't want to go longer than 3 years and the bank told them it was 3.9 percent APR and when we broke that down as you can see here it became B2 divided by 12 months, so it gave me interest per month.
Now, if I want to solve for you know how much of pri.. what price of car could he go looking at? In this you can simply type equals and I'm looking for present value, hit my open parentheses, first thing it asked me for was my rate, that was B5 comma. He told me he didn't want to go more than 36 months. He told me his payment was $200 which is B3 and that's all the information I have. And once you find out when you close the parentheses was, that he could spend $6784 on a car. Now, perhaps you're out looking at a car and you realize, well that car is $10000, I wonder what my monthly payment would be if I wanted to purchase that.
We can also solve it with a just a simple twist to the formula. This would be equals, in Excel a monthly payment or any kind of uniform amount is PMT, start your parentheses. First thing it asked you for was rate. That was B5 comma. You're going to go 36 months comma and the present value of the car was $10000. And that's all the information I have so I am closing the parentheses and that would tell me that my payment per month would be $294.80. Now say I wanted to look at the number of months that this would be to actually purchase this car. So to solve for n your solving for nPER. So in this case you can just put equals nPER, your rate was B5, your payment was 200, your present value was $10000 and that would tell you that if you followed what your guidelines were there that it would take you… ooh it shows a negative 46 months, I must have done something wrong here.
Price of the car. So let’s take a look at F2 here, put a negative in front of it and you will find, there that fixed it [chuckles]. You will find that you have 55 months. If you bought the $10000 car, stuck with that interest, and the $200 per month payment, it would take you wow! Almost 6 years to pay off that vehicle. So as you can see with Excel you can solve for variety of things with just a few pieces of information. And this is a very applicable way to try things out when you're interested in doing personal finances Excel works great for you. So in this tutorial that I showed you how to do a present value given an A, how to do an A given a present value and how to solve for an unknown of n.