Review of investment worksheet

8 Replies

I am trying to create an investment worksheet that will give me a quick overview of a potential property investment. My question is how do I account for the principal paid on the mortgage? The worksheet is attached in the link below - any feedback is appreciated.

https://www.dropbox.com/s/urz8bgzjpj5krvl/Investeringskalkulator.xls?dl=0

I couldn't give you the exact technical formula to do what you want to do.

However there are lots of free mortgage calculators that will do this for you.

Calculating the principal is not the problem (as you can see from the worksheet). My question is how do I address the positive impact paying the principal has. I know it can`t be counted as income, as it does not affect the cash flow. But how do I account for the decreasing total mortgage?

Originally posted by @Karl Sivert Skatland :

Calculating the principal is not the problem (as you can see from the worksheet). My question is how do I address the positive impact paying the principal has. I know it can`t be counted as income, as it does not affect the cash flow. But how do I account for the decreasing total mortgage?

Every year you're paying $X in principal payments, you are building $X in equity assuming the value remains the same. That equity will only be realized once the asset is sold or recapitalized. Assume a sale price in year Y, and those principal payments will be factored into your return on investment calculation because when you sell the asset you will be paying down a mortgage balance of (Original Amount - Principal Payments + Outstanding Interest). 

If you wanted to, you can calculate an unrealized return on equity (return on initial down payment + CC and any other expenses associated with the purchase) at any given point by adding your principal payments back to your total cash flow. Market value assumptions would sensitize your calculation 

You can also play around and model different recap scenarios

FYI you should be calculating RE asset returns on a pre-tax basis. What you are calling yearly ROI is referred to as cash on cash returns. In your case they are levered (use of mortgage). Levered CoC

Hope this helps.

Return on investment is an absolute return, i.e, a multiple of your original investment. What you are attempting to calculate is cash on cash return and an internal rate of return. Together, these 3 calculations will form the basis of analyzing an investment property. Try something like this for each year, model number of years you intend on holding i.e 3, 5, 7, 10, etc.

+PGI
-Vacancy
=EGI

Operating Expenses:
-Insurance
-RE Taxes
-Prperty Management
-Utilities
-Other Operating expense

NOI = EGI - Operating Expenses

Mortgage Payments:
-Principal
-Interest
CF after Debt Service

CapEx
-Capital Expenditures
CF after CapEx

-Purchase/+Sale
+Financing/-Paydown of remaining balance upon sale
Net Cash Flow

Calculate your returns off of Net Cash flow.

Returns:
Levered CoC: Year X CF/Equity Invested
ROI: Equity Invested/Total CF Distributions, including sale
IRR: Use excel function to calculate (=IRR(equityinvested, Year 1 CF, Year 2 CF, etc.)

Compare return metrics to other investment opportunities and pursue accordingly. 


Hope this helps, message me with any questions.

Originally posted by @Karl Sivert Skatland :

... My question is how do I address the positive impact paying the principal has. I know it can`t be counted as income, as it does not affect the cash flow. ...

Well, it is being paid from the income being received (rent), just like other expenses. The principal is an increase in your equity.  Paying principal does affect cash flow to you - it reduces the net cash flow you get to put into your pocket. Compare cash flow of a property with no mortgage to an identical property with identical rent and expenses but with a hypothetical zero percent mortgage; the principal payment reduces the spendable cash flow. 

Your problem is that you are never exiting your investment in the model.  You need to include a section that deals with the disposition of the property.  The exit is when you'll add the gains from equity back into cashflow.

Not what you asked about but also pertinent:

- Your ROI calculation should be based on total acquisition cost. You only have down payment in the denominator when you should have both down payment and any additional costs of acquisition.

- Your down payment is a negative cash outflow in year 0 and should be included in cashflow.


Also, a comment above correctly stated that you should be doing your calculations as pre-tax calculations, which I didn't change here.

Thank you so much for your responses - exactly what I was looking for! I`ll try to update the worksheet according to your corrections.