Before diving into the analysis process, it will be helpful to understand the terminology used throughout this article.
Cash Flow - A revenue and/or expense stream that occurs over a given period.
Example: An anticipated monthly income of $100k less $10k in expenses = $90K of cash flow each month.
Time Value of Money - This core principle of finance says that money is worth more the sooner it is received. So $1 received today is worth more than $1 received in the future because of its potential earning capacity.
Example: A 1 year rehab loan for $100,000 with a 5% interest rate will be worth $105,000 when it pays off one year from now. ($100K multiplied by 1.05%) Conversely, $100,000 received one year from now is only worth $94,240 today because of the lost earning capacity. ($100k divided by 1.05%)
Discount Rate - This is the opportunity cost of money and will be different for each investor. For example: bridge loans may require a different discount rate than rehab loans, etc.
What discount rate should you use? There are many finance theories about this topic, but it comes down to the deal and what else you or your firm can do with the money. If you have a history of earning a 9% rate of return over the past few years on private money transactions, then your firm’s discount rate may be 9%. Generally, the riskier the deal, the higher the discount rate.
Net Present Value (NPV) - The value of a cash flow stream in today’s dollars. This is calculated by applying a discount rate to the cash flow stream. The three necessary components of the calculation will be the amount of the cash flow stream, the timing of the stream, and the opportunity cost.
Begin analyzing any prospective investment with two basic steps:
- Estimate the anticipated cash flow stream over time;
- Calculate the NPV.
Estimate the anticipated cash flow stream
Let’s apply NPV analysis to the following scenario typical of rehab loans or bridge loans: $300,000 principal, 12% interest-only note, due in 5 years. The property posted as collateral is worth $400,000.
It is important to estimate the amount of cash flow the investment will generate as well as when the cash flow will be received.
Microsoft Excel® will be used in the following examples and user tips for this software for NPV analysis will be provided.
Here’s how the spreadsheet for this example would be structured:
Notice that the balance of $300,000 in cell B6 is negative because it is an outflow of cash for the purchase of the note. The $36,000 per year are the annual interest payments at 12%. In year 5, the principal of $300,000 is repaid, plus the full year of interest for a total of $336,000 . The net cash flow of $180,000 in cell B8 is the sum of the cash flows in cells B6 through G6.
Now that net cash flow has been determined, we will compare the difference between the $180,000 net cash flow in hand today vs receiving it over the next 5 years.
Calculate the Net Present Value (NPV)
There is a mathematical formula for calculating NPV but any spreadsheet program like Excel® will have the NPV function built in. What is important is that you understand how to interpret NPV, and how to use it to evaluate loan scenarios. There are three critical variables that go into NPV;
1) the cash flow stream;
2) the timing of the cash flow stream;
3) the discount rate.
In the current example we already determined the first two items. Determining the discount rate is more subjective and particular to each individual investor and loan scenario. Your opportunity cost of money may be more or less than someone else’s. Here are some tips for determining the discount rate to use for your analysis:
- Assess the amount of risk and the amount of time it would take you to manage the investment.
- Look to see what the marketplace is returning for similar investments. The rate you use may be different from another prospective investor’s because you may have different opportunities to invest in. The important thing is that you use the same rate when you are comparing similar investments.
- Look at what you or your firm has historically earned on similar investments.
- Look at what other opportunities may be available for the same investment.
For this example, we’ll use a 10% discount rate.
Armed with your three inputs: cash flow, cash flow timing and discount rate, we can now input the formula into the Excel® NPV formula fields to determine the result.
As shown by the Net Present Value display in cell B9, the investor, in today’s dollars, will yield $20,677 more with the proposed note investment as opposed to investing his funds at his discount rate of 10%.
If the loan being considered yielded only 7%, instead of the 12% in the previous example, and the discount rate was still 10%, the analysis would be as follows:
The Net Present Value results in a negative $31,016 which indicates the investor is better off earning their 10% opportunity cost, than investing in a note at 7%.
If an investor still wished to consider the $300,000 note, the investor may calculate the discounted note price to pay so that the yield would be the same return as the 10% opportunity cost of funds. Another way to word that is, essentially, we are attempting to figure out what note purchase price would result in an NPV of zero.
To do this, use Excel®’s Present Value (PV) formula as illustrated below.
The answer is that if the investor purchases the $300,000 note for a discounted purchase price of $265,883, the overall return will be 10%. Thus, the NPV is 0 because the two investments yield an equal return.
Blended Net Present Values
In most real world scenarios, there will be multiple possible outcomes. One way to evaluate a scenario with multiple outcomes is to assign a probability to each outcome and determine a weighted average of the result. This is called the ‘Probability Weighted Average of the Net Present Values’ or the ‘Blended NPV’s’.
The process works like this:
- Estimate the cash flow for each possible outcome.
- Calculate the NPV for each.
- Assign a probability % to each outcome.
- Multiply the probability % by the NPV.
- Add the results for each outcome.
Let’s look at two outcomes:
- The note performs throughout the entire term.
- The borrower defaults and there is a foreclosure.
You can have as many outcome possibilities as you wish, but for this example, we will stick to just two. We will use our original example of a $300,000, 12% interest-only note, due in 5 years, with the collateral property worth $400,000.
If the note performs (outcome 1), the cash flow will be as follows:
Remember, the $20,677 NPV means the investor would make that much more, in today’s dollars, than another investment that returns 10% ( the opportunity cost).
For outcome 2, assume there is a foreclosure which results in a cash flow as:
In this example, a foreclosure outcome results in a NPV of $8,264.
The next step is to assign a probability to each of the two outcomes. For instance, if the borrower has been paying regularly, has been steadily employed and is occupying this house then an investor might assume a 90% chance that the note will be paid on time. That leaves a 10% chance that an investor would have to foreclose. The sum of all probabilities you assign to each outcome must equal 100%.
Now multiply the probabilities for each outcome by that outcome’s previously calculated NPV:
So what does this mean? The net result, given the assigned probabilities, is that the considered investment will yield a better return than the opportunity cost of 10%.
Using Net Present Value analysis gives investors the ability to analyze many different outcomes to evaluate each note deal compared to the opportunity cost of the investor’s funds.