Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Financial Functions
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 |
#2
|
|||
|
|||
Financial Functions
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. -- Regards, Fred "Hague2" wrote in message ... 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 |
#3
|
|||
|
|||
Financial Functions
I think we're on the same page.
"Fred Smith" wrote: 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. -- Regards, Fred "Hague2" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Functions
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 "Fred Smith" wrote: 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. -- Regards, Fred "Hague2" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Functions
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". -- Regards, Fred "Hague2" wrote in message ... 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 "Fred Smith" wrote: 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. -- Regards, Fred "Hague2" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Functions
Hague2 wrote:
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Financial Functions
I wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
IS FVVAR AND PVVAR EXIST IN FINANCIAL FUNCTIONS? | Excel Worksheet Functions | |||
cell format for financial functions | Excel Discussion (Misc queries) | |||
Are financial functions calculated based on compound interest? | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |