Hague2 said:
I have an investment (property) that I want to know
when I will break even on (and start making a profit)?
For example: Property Value $80,000
Appreciation Rate 8% [;] Loan Amount $76,000
Interest Rate 6% [;] Loan Period (Years) 30
Negative Cash Flow (per month) $100 [;] Down Payment 4000
Closing Cost (included in the loan amount) $5000
The numbers seem inconsistent.
Closing costs are not "included in the loan amount". By
definition, closing costs are fees paid up-front along with
the down payment. Do you mean that the closing costs
are included in the purchase price ($85,000)?
Alternatively, do you mean that $80,000 is the purchase
price? If so, it is not the initial property value, since
closing costs are not included in the property value.
However, if that were the case and if the closing costs
were $5000, the initial property value would be $75,000.
That would be a contradiction of facts since it would be
less than the loan amount, not to mention the loan plus
down payment.
Finally, I am not sure what you mean by a "negative cash
flow" of $100/month. Well, I know what it should mean;
I am not sure you do. Perhaps you simply left out some
facts. Are you implying that someone is paying rent that
covers the loan payment (about $456, perhaps net the
interest tax deduction) plus other prorated costs (utilities,
insurance, property tax, maintenance, etc) less $100?
That is what "negative cash flow" would mean to me.
Can you help me with the proper Financial Function to use?
We probably could use financial functions to compute
everything. But I would be inclined to simply build a
table.
Without taking time-value into account, this is how I
would model the solution.
Column A has the loan period numbers, 0-360.
Column B has the cumulative property value for each
period: $80,000 in B1 (period 0); and =B1*(1+app) in B2,
copied down through B361. "App" is the monthly appreciate
rate. It is not simply 8%/12. It can be computed by
=RATE(12,,-1,1.08).
(Thus, the first month's appreciation is $515, not $533.
As you might know, the difference grows each month --
a difference of 8.7% and nearly $70,000 after 30 years.)
Column C has the monthly cost net any income. Based
on assumptions above, C2:C361 would have $100.
Alternatively, you might want to break out the loan
payment, other costs and rent income in separate columns
so that you can factor in inflation for the other costs.
Depending on local laws, you might not be able to increase
rent at the same rate to cover cost increases.
Column D has the cumulative (net) cost: $9000 in D1
(period 0) for the down payment ($4000) and closing
costs ($5000); =D1+C2 in D2, copied down through C361.
(The initial cost of $9000 -- esp. the $5000 closing cost
-- is the reason why you do not turn a profit as early as
the first month even though property appreciation exceeds
the negative cash flow.)
Column E has the cumulative profit. Conceptually, that
is: CurVal - CapGainTax - CumCosts - LoanBal. It can
be computed by (in E2, copied down through E361):
=B2 - (B2-$B$1)*15% - D2
- (76000 + CUMPRINC(6%/12,360,76000,1,ROW()-ROW($B$1),0))
Note: CUMPRINC() returns a negative number. That is
why I use "+" to compute LoanBal = Loan - CumPrinciple.
Of course, you can simply the formula by writing
=...-76000-CUMPRINC(...).
Based on that, I show profitability in the 12th loan
period for this example.
Did you intend to take time-value into account?
To take time-value into account, the first thing you
need to stipulate is the "discount" rate. For example,
3%, which is typically used to approximate inflation
over a long period of time.
If you are interested in the time-valued profit analysis
(PV), I can provide the necessary changes.
However, I would not take time-value into account to
compute real profit. After all, the IRS doesn't.
Time-value analysis is useful for comparing investment
alternatives.
