Financial Functions

H

Hague2

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

Can you help me with the proper Financial Function to use? Thank You
 
F

Fred Smith

It depends what you mean by breakeven. To an accountant, you are making money
from day one because the appreciation on the property ($533/month) is greater
than the operating loss ($100/month).

If you mean something different, let us know, and we'll help you calculate it.
 
H

Hague2

I think we're on the same page.


Fred Smith said:
It depends what you mean by breakeven. To an accountant, you are making money
from day one because the appreciation on the property ($533/month) is greater
than the operating loss ($100/month).

If you mean something different, let us know, and we'll help you calculate it.
 
H

Hague2

Hello, I guess we're not on the same page. I realize you're making monies
with the appreciation (on paper), but I still would like to know the formula
that includes all the criteria I listed. I can see the monies being
distributed in an Amortization table, would this work about the same? I'm
trying to decide how long to hang on to the property. Thanks again, Nita
 
F

Fred Smith

I can't help you because I can't follow your criteria. As we've agreed, if you
include the appreciation of the property, you're making money from day one.

If you don't include the property appreciation, you're always losing money,
because you have a constant negative cash flow.

You'll need to be more specific about what you mean by "break even".
 
J

joeu2004

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.
 
J

joeu2004

I said:
Closing costs are not "included in the loan amount". By
definition, closing costs are fees paid up-front along with
the down payment.

Not 100% correct. __Some__ closing costs are considered
prepaid finance charges, which reduce the loan amount.
But the buyer still needs to cover those charges up-front in
order to meet the purchase price.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top