Build a Home Buying Investment e-Analyser by Numpy in Colab
Home buying investment is usually analysed by Net Present Value (NPV) and Internal Rate of Return (IRR). NPV and IRR are common metrics for investment assessment and these functions are also available in Excel or ARGUS (Figure 1).
This article shows an example of building a NPV and IRR e-Analyser by using numpy in Colab. It compares three investment scenarios of buying a house to earn rental incomes for 10 years:
- pay by 100% loan;
- pay by 100% saving; and
- pay by 20% downpayment and 80% mortgage loan.
First, we install numpy-financial and import npv, irr functions as follows:
!pip install numpy-financial
import numpy as np
import numpy_financial as npf
from numpy_financial import npv, irrWe use @param to input parameters, including discount rate p.a. (disc_rate), loan interest rate p.a. (loan_rate), mortgage interest rate p.a. (mort_rate), home price in NZD (home_price), expected weekly rental income in each year (exp_wk_rent_y1, exp_wk_rent_y2, …), expense rate p.a. (expense_rate), and expected sale price in the end of year 10 in NZD (exp_sale_price_y10). Rates are input by slider, numeric data are input by entering integers.
#see https://colab.research.google.com/notebooks/forms.ipynb#scrollTo=eFN7-fUKs-Bu
#@title Cash Flows - INPUT DATA
disc_rate = 5 #@param {type:"slider", min:0.00, max:10.00, step:0.05}
loan_rate = 4.0 #@param {type:"slider", min:0.00, max:10.00, step:0.05}
home_price = 1000000 #@param {type:"integer"}
exp_wk_rent_y1 = 500 #@param {type:"integer"}
exp_wk_rent_y2 = 500 #@param {type:"integer"}
exp_wk_rent_y3 = 550 #@param {type:"integer"}
exp_wk_rent_y4 = 550 #@param {type:"integer"}
exp_wk_rent_y5 = 580 #@param {type:"integer"}
exp_wk_rent_y6 = 580 #@param {type:"integer"}
exp_wk_rent_y7 = 600 #@param {type:"integer"}
exp_wk_rent_y8 = 600 #@param {type:"integer"}
exp_wk_rent_y9 = 650 #@param {type:"integer"}
exp_wk_rent_y10 = 650 #@param {type:"integer"}
expense_rate = 15.0 #@param {type:"slider", min:0.0, max:30.0, step:0.5}
exp_sale_price_y10 = 1200000 #@param {type:"integer"}Case 1:
In case 1, 100% home price is paid by a bank loan which is assumed to be repaid once every year in simple interest. It does not require any initial cash outflow, but it requires interest payment every year, and in the end of the 10th year, it requires a full repayment of the loan amount to the bank. In this case, it shows that at 4% simple interest rate, it requires an annual repayment of $40,000 for borrowing $1m.
#Loan Interest Calculator - yearly loan interest repayment amount as cash outflows
yr_loan_expense = home_price*(loan_rate/100)
#Net Yearly Rental Income Calculator - yearly net rental income amount as cash inflows
nyrent1_c1 = exp_wk_rent_y1*52*(1-expense_rate/100)-yr_loan_expense
nyrent2_c1 = exp_wk_rent_y2*52*(1-expense_rate/100)-yr_loan_expense
nyrent3_c1 = exp_wk_rent_y3*52*(1-expense_rate/100)-yr_loan_expense
nyrent4_c1 = exp_wk_rent_y4*52*(1-expense_rate/100)-yr_loan_expense
nyrent5_c1 = exp_wk_rent_y5*52*(1-expense_rate/100)-yr_loan_expense
nyrent6_c1 = exp_wk_rent_y6*52*(1-expense_rate/100)-yr_loan_expense
nyrent7_c1 = exp_wk_rent_y7*52*(1-expense_rate/100)-yr_loan_expense
nyrent8_c1 = exp_wk_rent_y8*52*(1-expense_rate/100)-yr_loan_expense
nyrent9_c1 = exp_wk_rent_y8*52*(1-expense_rate/100)-yr_loan_expense
nyrent10_c1 = exp_wk_rent_y10*52*(1-expense_rate/100)-yr_loan_expense
print ("Yearly Loan Interest Expense, NZD", np.round(yr_loan_expense,2))Yearly Loan Interest Expense, NZD 40000.0
As there are only 10 years’ figures for cash flows, we simply use np.array to define the cash flow series of case 1 (cf_c1). All rental incomes are considered positive cash flows. In the end of the 10th year, the cash flow is the difference between the sale price and the home purchase price (exp_sale_price_y10-home_price). Whether it is a positive or negative cash flow depends on the whether sale price in the end of the 10th year is higher or lower than the purchase price. With the array of cash flows provided, then we can estimate the NPV and IRR of the investment scenario by using numpy_financial’s function npf.npv and npf.irr. Here the example shows that the NPV and IRR for case 1 are $6752.27 and 6.13%. It implies that, with all these assumed parameters, borrowing 100% loan to buy a house does not make much profit.
#DCF of Rental Incomes
cf_c1 = np.array([0, nyrent1_c1, nyrent2_c1, nyrent3_c1, nyrent4_c1, nyrent5_c1, nyrent6_c1, nyrent7_c1, nyrent8_c1, nyrent9_c1, nyrent10_c1+(exp_sale_price_y10-home_price)])
NPV_c1= npf.npv(disc_rate/100, cf_c1)
IRR_c1= npf.irr(cf_c1)
print ("Net Present Value in NZD", np.round(NPV_c1, 2))
print ("Internal Rate of Return in %", np.round(IRR_c1*100, 2))Net Present Value in NZD 6752.27
Internal Rate of Return in % 6.13
Case 2:
In case 2, if the house is bought by paying 100% home price in cash (saving), then the initial cash outflow will be the house price. Yet, it does not require interest payment each year, and the cash flow in the end of the 10th year does not require any loan repayment. Thus, the annual rental incomes are estimated as follows:
nyrent1_c2 = exp_wk_rent_y1*52*(1-expense_rate/100)
nyrent2_c2 = exp_wk_rent_y2*52*(1-expense_rate/100)
nyrent3_c2 = exp_wk_rent_y3*52*(1-expense_rate/100)
nyrent4_c2 = exp_wk_rent_y4*52*(1-expense_rate/100)
nyrent5_c2 = exp_wk_rent_y5*52*(1-expense_rate/100)
nyrent6_c2 = exp_wk_rent_y6*52*(1-expense_rate/100)
nyrent7_c2 = exp_wk_rent_y7*52*(1-expense_rate/100)
nyrent8_c2 = exp_wk_rent_y8*52*(1-expense_rate/100)
nyrent9_c2 = exp_wk_rent_y8*52*(1-expense_rate/100)
nyrent10_c2 = exp_wk_rent_y10*52*(1-expense_rate/100)In the cash flows array of case 2, the initial cash outflow is -home_price, then each years’ rental income is positive cash inflow, and the last positive cash inflow is the sale price. In this example, NPV and IRR of case 2 become -$70,465.08 and 4.16%. It is a negative NPV investment with an IRR less than the discount rate.
#DCF of Rental Incomes
cf_c2 = np.array([-home_price, nyrent1_c2, nyrent2_c2, nyrent3_c2, nyrent4_c2, nyrent5_c2, nyrent6_c2, nyrent7_c2, nyrent8_c2, nyrent9_c2, nyrent10_c2+exp_sale_price_y10])
NPV_c2= npf.npv(disc_rate/100, cf_c2)
IRR_c2= npf.irr(cf_c2)
print ("Net Present Value in NZD", np.round(NPV_c2, 2))
print ("Internal Rate of Return in %", np.round(IRR_c2*100, 2))Net Present Value in NZD -70465.08
Internal Rate of Return in % 4.16
Case 3:
In case 3, if the house is purchased by borrowing 80% home price by mortgage, i.e. 20% downpayment, then we include the mortage rate here as 2.55% p.a. and define mortage loan amount (mort_loan) as 80% of home price. To simplify the scenario, we assume that the repayment period is 10 years, i.e. when the house is resold, the owner does not require any repayment to the bank in the end of the 10th year. We make use of the Mortgage Calculator function npf.pmt which has been elaborated in Yiu (2021). The yearly mortgage repayment amount is found to be NZ$90,717.54
mort_rate = 2.55
mort_loan = home_price*(1-0.2)
repay_yr = 10
pmt_c3 = npf.pmt(mort_rate/1200, repay_yr*12, -mort_loan)
print ("Yearly Repayment Amount in NZD", np.round(pmt_c3*12,2))Yearly Repayment Amount in NZD 90717.54
In case 3, the net rental income will have to deduct mortgage repayment amount. Here we assume that the mortgage repayment amount is fixed over time.
nyrent1_c3 = exp_wk_rent_y1*52*(1-expense_rate/100)-pmt_c3*12
nyrent2_c3 = exp_wk_rent_y2*52*(1-expense_rate/100)-pmt_c3*12
nyrent3_c3 = exp_wk_rent_y3*52*(1-expense_rate/100)-pmt_c3*12
nyrent4_c3 = exp_wk_rent_y4*52*(1-expense_rate/100)-pmt_c3*12
nyrent5_c3 = exp_wk_rent_y5*52*(1-expense_rate/100)-pmt_c3*12
nyrent6_c3 = exp_wk_rent_y6*52*(1-expense_rate/100)-pmt_c3*12
nyrent7_c3 = exp_wk_rent_y7*52*(1-expense_rate/100)-pmt_c3*12
nyrent8_c3 = exp_wk_rent_y8*52*(1-expense_rate/100)-pmt_c3*12
nyrent9_c3 = exp_wk_rent_y8*52*(1-expense_rate/100)-pmt_c3*12
nyrent10_c3 = exp_wk_rent_y10*52*(1-expense_rate/100)-pmt_c3*12In case 3, the initial cash outflow is the downpayment (i.e. home price — mortgage loan), then each year’s rental income is cash inflow. In the end of the 10th year, the sale price is the cash inflow. In this example, the NPV and IRR of case 3 become $29,038.11 and 5.66%. It is a bit better than both case 1 and case 2.
#DCF of Rental Incomes
cf_c3 = np.array([-(home_price-mort_loan), nyrent1_c3, nyrent2_c3, nyrent3_c3, nyrent4_c3, nyrent5_c3, nyrent6_c3, nyrent7_c3, nyrent8_c3, nyrent9_c3, nyrent10_c3+exp_sale_price_y10])
NPV_c3= npf.npv(disc_rate/100, cf_c3)
IRR_c3= npf.irr(cf_c3)
print ("Net Present Value in NZD", np.round(NPV_c3, 2))
print ("Internal Rate of Return in %", np.round(IRR_c3*100, 2))Net Present Value in NZD 29038.11
Internal Rate of Return in % 5.66
We print a comparison table showing the annual cash flows of the three cases as follows:
print (" ", "Case 1", "Case 2", "Case 3")
print ("Initial Cash Outflow ", "0.00", np.round(-home_price), np.round(-(home_price-mort_loan)))
print ("Net Rental Income in Year 1, NZD", np.round(nyrent1_c1,2), np.round(nyrent1_c2,2), np.round(nyrent1_c3,2))
print ("Net Rental Income in Year 2, NZD", np.round(nyrent2_c1,2), np.round(nyrent2_c2,2), np.round(nyrent2_c3,2))
print ("Net Rental Income in Year 3, NZD", np.round(nyrent3_c1,2), np.round(nyrent3_c2,2), np.round(nyrent3_c3,2))
print ("Net Rental Income in Year 4, NZD", np.round(nyrent4_c1,2), np.round(nyrent4_c2,2), np.round(nyrent4_c3,2))
print ("Net Rental Income in Year 5, NZD", np.round(nyrent5_c1,2), np.round(nyrent5_c2,2), np.round(nyrent5_c3,2))
print ("Net Rental Income in Year 6, NZD", np.round(nyrent6_c1,2), np.round(nyrent6_c2,2), np.round(nyrent6_c3,2))
print ("Net Rental Income in Year 7, NZD", np.round(nyrent7_c1,2), np.round(nyrent7_c2,2), np.round(nyrent7_c3,2))
print ("Net Rental Income in Year 8, NZD", np.round(nyrent8_c1,2), np.round(nyrent8_c2,2), np.round(nyrent8_c3,2))
print ("Net Rental Income in Year 9, NZD", np.round(nyrent9_c1,2), np.round(nyrent9_c2,2), np.round(nyrent9_c3,2))
print ("Net Rental Income in Year 10, NZD", np.round(nyrent10_c1,2), np.round(nyrent10_c2,2), np.round(nyrent10_c3,2))
print ("Final Net Cash Flow in the end, NZD", np.round(exp_sale_price_y10-home_price), np.round(exp_sale_price_y10), np.round(exp_sale_price_y10))It is quite clear that case 1 target at capital gain, and the source of positive NPV relies solely on the house price increase after the investment period. In other words, if house price falls, case 1 scenario will make a loss.
In contrast, case 2 does not borrow, and the source of a positive NPV comes from both the net rental incomes and the capital gain. If rental is growing fast, case 2 scenario can make better profit.
Case 3 is an in-between, as it just borrows 80% home price, and the mortgage repayment is not interest-only, but includes partially principle repayment. Thus, even though each year’s net rental income is negative, but they are paying back the principle to the bank gradually. It does not require a large initial cash outflow, which can be considered as a risk sharing with the bank.
Case 1 Case 2 Case 3
Initial Cash Outflow 0.00 -1000000 -200000.0
Net Rental Income in Year 1, NZD -17900.0 22100.0 -68617.54
Net Rental Income in Year 2, NZD -17900.0 22100.0 -68617.54
Net Rental Income in Year 3, NZD -15690.0 24310.0 -66407.54
Net Rental Income in Year 4, NZD -15690.0 24310.0 -66407.54
Net Rental Income in Year 5, NZD -14364.0 25636.0 -65081.54
Net Rental Income in Year 6, NZD -14364.0 25636.0 -65081.54
Net Rental Income in Year 7, NZD -13480.0 26520.0 -64197.54
Net Rental Income in Year 8, NZD -13480.0 26520.0 -64197.54
Net Rental Income in Year 9, NZD -13480.0 26520.0 -64197.54
Net Rental Income in Year 10, NZD -11270.0 28730.0 -61987.54
Final Net Cash Flow in the end, NZD 200000 1200000 1200000I have uploaded my NPV IRR e-Analyser at the following Github: https://github.com/Chung-collab/GREAT-LAB/blob/main/NPV_IRR.ipynb for knowledge co-creation.
I also produce a Youtube at (Yiu, 2021b) to explain in more details.
The NPVs and IRRs can be worked out in Excel by using DCF formula and What-if Command, Figure 2 shows a template that I create to compare the results:
All the three cases got almost the same NPVs and IRRs using either numpy-financial in Colab or formula and what-if command in Excel. It shows what the packages’ functions npf.npv and npf.irr achieve.
[Caveat: this is just my trial and the estimate is not sophisticated enough to discuss for real life estimations. Please do not consider the results as any advice on home buying decisions.]
References
Yiu, C.Y. (2021a) Build My First Mortgage Calculator by Numpy in Colab, Medium, July 10. https://ecyy.medium.com/build-my-first-mortgage-calculator-by-numin-colab-d5390cbb6b9
Yiu, C.Y. (2021b) Build a NPV and IRR home buying investment decision e-Analyser by Numpy in Colab, Youtube, Jul 15. https://youtu.be/8cMcCh5TsI8
