For this video we’ll continuing our Financial math series in Excel. We will simulate a simple rental property to apply some new formulas and calculate the impact of inflation on our investments. We are trying to figure out if the cash that comes with owning an appreciating property justifies a mortgage loan to buy said property. The financial formulas in Excel we’ll be learning will be: Future Value (VF), Rate, NPV (Net present value) and IRR (Internal Rate of return).
We will start by evaluating the rental terms we can expect from our property. This is done via a simple market investigation on the internet. We’ll end up choosing a conservative scenario with rents on the lower end of the spectrum, just to be safe.
Next, we’ll try to predict where our home values will be through the next 15 years (the duration of the loan) and we’ll try to say they’ll be just above inflation. This means that for the next 15 years, our house will more than double in price.
Once this is done, we can begin to put together our financial run, where we will calculate the cashflow from the rents and the property value appreciation every month. For this, we need to use the Rate formula in Excel to figure out what the monthly growth rate for our property value is. We also need to do a simple division in Excel to figure out the monthly rental rate.
Once the cashflow table has been set up, we need to use the IF formula in Excel and the MOD formula to make sure that rents stay the same for every 12 months, instead of increasing every month (that would be illegal!). We’ll lose some rent money, but not enough to make a dent in our plans.
We are going to be using a couple of Excel Sum formulas to add up the total cash that comes in from rents, and the extra value that comes in from property appreciation. Adding that up to the present value of our property, we’ll get up to a million dollars of value in our property.
To finish, we need to discount the effects of inflation from our calculations. This is very easy to do in Excel. We’ll use the NPV (net present value) formula in Excel to discount inflation from our cashflows and figure out the current value of our property as it stands.
We will also then figure out the IRR (Internal rate of return) of the project so we can compare it against what the bank is charging us for the loan. Again, Excel makes calculating the IRR a very simple and painless process.