Continued from…
Using time value of money for real estate valuation [Part 1-2]
How to use excel to make real estate calculations?
Excel has many financial features that can aid you with any real estate calculation. You can utilize one of them here.
Let’s start.
Step 1
Begin by creating a new Excel worksheet.
Step 2
Select “Function” from the “Insert” menu that appears when you pull down the “Insert” menu. You’ll see something like this in the window:
Step 3
You’ll find function categories in the left-hand pane. Choose “Financial.”
And then, select NPER; it is a financial function in Excel that you may use to calculate the number of periods in various investment scenarios.
Click “OK” after selecting that function in the right-hand window.
Step 4
Next, you’ll see a window where you can enter values for each variable that this function might utilize.
For our example, not all of the variables are required. Because you haven’t made any payments, this can be overlooked.
The “type” specifies whether payments are made at the start or end of each period; the function defaults to the most common option so that you can disregard it as well. You must fill in the following information:
- The “Rate” is .04 (the decimal equivalent of 4 per cent)
- The “PV” as -200000 (note that you must express the PV as a negative number since PV is the money going out and FV is money coming in from the function’s flow-of-funds investment perspective; also note that you do not use commas within the numbers)
- The FV is set to 400000.
Step 5
You’ll notice the formula result, 17.67299, at the bottom of the window. It means that at a 4% annual growth rate, it will take 17.67 years for the value of this property to increase from $200,000 to $400,000.
When you click the “OK” button, the window closes, and the result shows in the cell where your pointer was when you started the exercise. That cell now has a formula in it, which was generated by the form you filled out:
= NPER(0.04, -200000,400000)
You can skip the form and write a formula like this right into the cell if you become an Excel “superuser” in the future. You can calculate numerous financial functions using a form like the one above.
Learn More
What is Discounted Cash Flow?
The discounted cash flow calculation considers the time value of money by discounting future cash flows back to the present. DCF analysis uses a discount rate that reflects the risks associated with the cash flows and the expected time."
When valuing a property, real estate professionals often use discounted cash flow (DCF) technique.
How to calculate Discounted Cash Flow (DCF)?
You can calculate DCF in three steps:
Step 1
Forecast the expected cash flows from the investment.
Step 2
You choose a discount rate, which depends on the cost of funding the investment or the opportunity cost of other investments.
Step 3
Using a financial calculator, a spreadsheet, or a manual computation, discount the predicted cash flows back to the present day.
You are missing out if you haven’t yet subscribed to our YouTube channel.
Calculate the present value of a lump sum
The present value of future lump sum (i.e. one-time) receipts is calculated using this technique. Assume the investor has been provided with an investment opportunity to generate a $1,543.30 cash inflow over the next six years, as indicated in panel A below.
Assume that the investor expects to earn 7.5% on identical investments, compounded annually. The 7.5% discount rate can be considered an opportunity cost—the return that an investor foregoes on a risk-free alternative investment to engage in the current opportunity.
How much can the investor pay today for this $1,543.30 future lump sum payment and make a 7.5% profit?
To solve this problem, you need to convert a future lump sum amount receipt into a present value.
Present value of lump sum formula
PV = FV X PVF (7.5%, six years)
= $1543.30 X 0.65
= $1,000.00
If he can get a 7.5% annual return on similar assets, the investor should be willing to spend up to $1,000 today to receive $1543.30 at the end of six years.
The keystrokes for the calculator are as follows:
With these values, your calculator should return -$1,000. Because calculators require the matching of inflows (+) and outflows (-) to do TVM calculations, present values are displayed as negative amounts (assuming the future value is provided as a positive number).
Quick Tip
Try it Now
You are considering the acquisition of some raw land. If the property is estimated to be worth $1000,000 in 20 years, what is the present value of this investment? Assume there would be no intermittent financial inflows or outflows and that the investor expects to receive a 7 per cent yearly return on such assets.
You can use the discounting cash flow calculation to help you assess the profitability of a potential investment.
Here are the top property investment strategies that you should know.
Property Development Feasibility Study Bundle
Includes 5 x detailed eBooks (193 pages)
✓ Property Development Feasibility Study [THE KEY] - (45 pages)
✓ Real Estate Development ProForma - Ultimate Guide - (39 pages)
✓ Residual Value Of Land Vs Profit Margin - The Winner - (24 pages)
✓ Preliminary Development Feasibility Assessment - (35 pages)
✓ How To Choose a Property Development Feasibility Template? - (50 pages)
Calculate the present value of the annuity
Assume that investors are presented with an investment opportunity to receive $1,543.30 at the end of each year for the next six years, as illustrated in panel B of the timeline presentation for discounting problems.
Investors that need a 7.5 per cent yearly return should know how much they may spend for this annuity and still get the 7.5 per cent return they need.
There are two methods for translating an annuity into a present value. One method calculates the present value of six lump-sum amounts for each year and then adds the figures together.
Calculating the present value of an annuity is a less time-consuming option (PVA). This alternate operation’s conversion equation is
PVA = Annuity X PVFA (7.5%, six years)
= $1,543.30 X 4.694
= $7,244.01
Keystrokes required for this calculation are -
The annuity amount is placed into the PMT register because the cash flows will be received every year for the next six years. There is no fixed quantity (FV).
What if the payments are due at the start of each calendar year? The present value of this annuity is $7,787.31, which may be computed using the “begin mode” on your calculator or by increasing the present value of the standard annuity by an additional year’s worth of interest.
[i.e., $7,787.31 = $7,244.01 (1 + 0.075)].
Quick Tip
Try it Now
You just bought 125 shares of a publicly-traded real estate company that specializes in apartment complexes. The corporation is scheduled to pay $2.25 per share in quarterly dividends.
What is the value of the stock to you now if your needed yearly rate of return is 16 per cent, and you expect the stock to be worth $80 per share at the end of six years?
How to calculate IRR?
Now we know,
- how to calculate future values when the amount to be invested is known (compounding) and
- how to arrive at current values while future values are known or assumed (i.e., discounting).
You can use the TVM concept to calculate real estate investment rates of return, which is a critical extension.
Consider the future value of a lump sum problem we looked at earlier. We found that if $1,000 is deposited today for six years in an investment vehicle that earns 7.5% compounded annually, the account amount at the end of five years will be $1,543.30.
As a result, making this investment is equivalent to earning a 75% return. The investment yield or internal rate of return (IRR) are terms used to describe this rate of return.
Consider the present value of an annuity problem in which investors were offered the chance to receive $1,543.30 at the end of each year for the next six years.
The current value of future cash flows is $7,244.01 if investors believe they can get a 7.5% return on equivalent risk assets.
What kind of return or IRR can investors expect if they invest $7,244.01 in this opportunity? Obviously, on their $7,244.01 investment, investors will get a 7.5% internal rate of return. The keystrokes on the calculator are
The above solution process will not function if the periodic cash flows from owning and renting real estate assets are not projected to remain consistent over time. Instead, investors must use spreadsheet software or their financial calculators’ cash flow key.
The internal rate of return (IRR) is crucial in real estate investment and finance. It expresses the return on investment throughout the entire investment holding term as a compound interest rate.
On similar projects of comparable risk, it is easy to compare the computed (or “going-in”) IRR to the investor’s needed IRR. If an investment opportunity’s going-in IRR exceeds the investor’s necessary rate of return, the venture should be pursued. If the IRR at the outset is less than the investor’s necessary rate of return, avoid the investment and explore other options.
Learn More
Mortgage calculations
Understanding mortgage calculations is critical when purchasing a property. To make an informed decision, you need to know how much your monthly payment will be, how much money you will need for a down payment and the loan terms.
Fortunately, several online calculators can help you with these calculations.
One of the most important mortgage calculations is the monthly payment.
This calculation tells you how much money you need to pay each month to repay your loan over 30 years.
To calculate your monthly payment, you need to know three things:
- the amount of the loan
- the interest rate
- And the number of months in the loan.
Monthly payment calculation formula
IMPORTANT
PMT = (L x i) / [(r x n) – (l x i)]
Monthly payment calculation example
Suppose you have a 30-year mortgage with an interest rate of 12%. To calculate your monthly payment, you would use the following formula:
IMPORTANT
PMT = (L x i) / [(r x n) – (l x i)]
PMT = (30,000 x .12) / [(.12 x 360) – (30,000 x .12)]
PMT = $405.53
This means that your monthly payment would be $405.53.
To purchase a property, you will also need to know how much money you will need for a down payment.
Down payments can range from 0% to 20% of the property’s purchase price. To calculate your required down payment, you need to know the purchase price of the property and the loan-to-value ratio.
IMPORTANT
Down Payment = (Purchase Price – Loan Amount) / (Loan Amount x 100)
Down payment calculation example
Suppose you want to purchase a property for $200,000. To calculate your required down payment, you would use the following equation:
Down Payment = (200,000 – 100,000) / (100,000 x 100)
Down Payment = $20,000
You will need to provide a down payment of $20,000 to purchase the property.
Finally, it is important to understand the loan terms before you sign on the dotted line. The most common term for a mortgage is 30 years. However, you can also choose a 15-year or 20-year mortgage.
Learn More
The Bottom Line
As you can see, the time value of money is a critical concept in real estate valuation methods. By understanding how to calculate present and future values, you can make better decisions about your investments.
In addition, by using Excel to perform these calculations or real estate valuation method, you can become more comfortable with the numbers and better understand what goes into making sound real estate investment decisions.
Enroll on one of the structured property development courses to make your next project a success.
FAQs
What is TVM in real estate?
TVM, or Time Value of Money, is one of the most important concepts in real estate investment. It’s simply the idea that money is worth more now than it will be in the future.
This may seem like common sense, but it has powerful implications for real estate investing.
In real estate, this principle applies in two ways. First, if you can buy a property now or wait for a bit, you should always choose to buy it now because the value of that property will only go up in the future.
Second, when looking at investment properties, you should always factor in how much money you could earn by investing that money elsewhere.
What is the example of the time value of money?
One example of the time value of money is when you’re offered a choice between being paid $10 today or $12 tomorrow.
Most people would choose to be paid $10 today because they would rather have the money in their hand right now than wait an extra day for an extra $2.
That’s because the $10 today is more valuable to them than the $12 tomorrow since they can use the $10 today to buy something that they want right now.
