Anyone that deals with cash flows know that there is not a guarantee that someone will pay on time. Pretend you are a CFO and your company will offer a line of credit to customers to assist in a seasonal business. This means that the company needs to borrow product from your company in July and will pay you back for that product throughout the year. We must figure out how much credit to give them and what the expected return on that should be based on risk.
We use XNVP because this is not a steady cash flow. We have seen the customers books and we can tell when they would likely have the additional capital to pay us back.
Here is the formula
=XNPV(A2,B4:B10,A4:A10)
We offer them the credit in March and figure out a good calculation as to when they can make the rest of their payments based on trends. With the NPV formula it assumes that all of the payments are made evenly whereas this is not the case and customers line of credit should not be impacted based on uneven payment. So, let’s say we know that they can afford a line of credit of $6,000 and realistically pay this back by September first. Great, but we still need to determine how much that is worth today. We know that this is risky but it’s not too risky so we go with 10% today that would be worth $5,802.45 of product to get a return of $6,000.
DiG with Data 