Equivalence For Repeated Cash Flows

Video titled: Chapter 4 Video: Unknowns Using A

Transcript Area

In this Excel tutorial I'm going to show you how to solve for two different unknowns, interest and n when you're dealing with A’s. Some information that I have here, say for instance one of the kids borrowed $1000 from me and I let them pay me back in $85 monthly payments and they're going to pay me for 15 months. What interest am I charging? It might be interesting for the child to know what this is too. So if you want to solve for interest, you can simply put your cursor in the box and start a formula equals interest in Excel is expressed as rate, so I just type A R T E , if you’re ever curious whether you got the right thing, notice it gives you the definition here.

Start my parentheses and here's my information. How many n’s are there? That is in B4 comma. What payment are they making me? Remember payment is in A in Excel that was $85 comma. I don't know if you all have noticed as we've been doing these but if you notice how these are color coded so the B4 is in blue, and the box that is B4 is in blue, B3 is in green, the boxes in green. They color code these so you can see exactly where you are in the spread sheet, which is a very cool thing. Okay, so I'm up to a present value, they borrowed $1000 and that's all I know, so I close my parentheses and it turns out I'm charging them about 3.2 percent interest.

Not bad, not loan shark rates which is good. Now let's try this a different way, say the kid borrows $1000 they agreed to make $55 per month payments and I'm going to charge him 0.01 percent interest per payment, and they are making monthly payments. How long is it going to take them to pay this this loan back to me? If you want to solve for n, it's simply equal NPER is n in Excel, my rate was in F4. The payment that they're going to pay me was $55 a month, my PV was the amount that they borrowed, which was $1000 and that's all I have. It turns out we went this way it would take them 18 months to pay the loan back. So, in this example I've shown you how when you have A’s that you can solve for other unknown such as interest and n.

Back to top