Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Excel Again: Projecting Slope of Future Expenses
Old 10-11-2009, 10:39 AM   #1
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,895
Excel Again: Projecting Slope of Future Expenses

Yes, it's yet another humble appeal for help from me, the Excel newbie/weenie :

I am trying to project investment property expenses over time, using the ratio of yearly expenses to the property's original cost. Example: The property was bought in 2003 for $157,000.00. In 2003, the expenses (not including depreciation) totaled $9668, or about 6% of the purchase price.

Excel tells me that the slope of the expense trend line from 2003-2009 is
y=.002x + .062

How can I use this equation to project yearly expense ratio through 2014? (I found a SLOPE function that doesn't seem to apply).

(NOTE: If somebody knows a better/smarter way to project future expenses, please feel free to share!!)

Thank you,

Amethyst
__________________

__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 10-11-2009, 11:17 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Midpack's Avatar
 
Join Date: Jan 2008
Location: Chicagoland
Posts: 11,977
Quote:
Originally Posted by Amethyst View Post
Yes, it's yet another humble appeal for help from me, the Excel newbie/weenie :

I am trying to project investment property expenses over time, using the ratio of yearly expenses to the property's original cost. Example: The property was bought in 2003 for $157,000.00. In 2003, the expenses (not including depreciation) totaled $9668, or about 6% of the purchase price.

Excel tells me that the slope of the expense trend line from 2003-2009 is
y=.002x + .062

How can I use this equation to project yearly expense ratio through 2014? (I found a SLOPE function that doesn't seem to apply).

(NOTE: If somebody knows a better/smarter way to project future expenses, please feel free to share!!)

Thank you,

Amethyst
I'd be happy to help you, but I can't figure out what you have here. The equation you've provided looks like a linear regression of your data, but if x=year and y=expenses, I can't make it get anything like $9668 for 2003 expenses. PM if you'd like, I'd be happy to help without getting into your business...
__________________

__________________
No one agrees with other people's opinions; they merely agree with their own opinions -- expressed by somebody else. Sydney Tremayne
Retired Jun 2011 at age 57

Target AA: 60% equity funds / 35% bond funds / 5% cash
Target WR: Approx 2.5% Approx 20% SI (secure income, SS only)
Midpack is offline   Reply With Quote
Old 10-11-2009, 11:29 AM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Gone4Good's Avatar
 
Join Date: Sep 2005
Posts: 5,381
Yeah, I'm not sure how helpful a linear regression is in this case. But if you want to apply the results, it seems to be telling you to increase the expense growth rate (6.2%) by 0.2% (20 bps) each year. So in 10 years, you'd be using an 8.2% growth rate.

I'd just assume expenses grow by the historic average, unless you have a specific reason to expect the growth rate will actually be higher or lower than what you've experienced in the past.
__________________
Gone4Good is offline   Reply With Quote
Old 10-11-2009, 12:47 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,019
Amethyst, if you are just looking for the how to manual, see the attachment.
__________________
Meadbh is offline   Reply With Quote
Old 10-11-2009, 01:46 PM   #5
Recycles dryer sheets
fisherman's Avatar
 
Join Date: Jul 2007
Posts: 478
When I was doing rental property I tried to alway keep the calculations as simple as possible.
I just listed all items in the house that would need to be replace within 15 years and then based upon standard life tables for items like refrigerators I just figured up when it was likely to fail based upon its current age and then put in enough money each month to save up the cost of a new one by that date. I would also plan to repaint, re-plumb, and re-roof along the same lines. This fund alway had enough to cover any expenses since it was enough items that could fail that I could cover the average. I also did all the maint. so I was only having to cover replacement items. The percent it worked out to per year based upon purchases price as 2.2% of purchase price if that helps.
__________________
Worked the plan and now living the Dream!
fisherman is offline   Reply With Quote
Old 10-11-2009, 06:56 PM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Midpack's Avatar
 
Join Date: Jan 2008
Location: Chicagoland
Posts: 11,977
Amethyst: Here is what I come up with based on the data you sent me. I did a linear regression on that data, and projected/extrapolated out to 2014. More than happy to explain further if you'd like. Or is someone else has a better approach, here's the data...
Attached Files
File Type: xls Property Analysis.xls (35.5 KB, 14 views)
__________________
No one agrees with other people's opinions; they merely agree with their own opinions -- expressed by somebody else. Sydney Tremayne
Retired Jun 2011 at age 57

Target AA: 60% equity funds / 35% bond funds / 5% cash
Target WR: Approx 2.5% Approx 20% SI (secure income, SS only)
Midpack is offline   Reply With Quote
Old 10-11-2009, 07:21 PM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,895
Quote:
Originally Posted by Midpack View Post
Amethyst: Here is what I come up with based on the data you sent me. I did a linear regression on that data, and projected/extrapolated out to 2014. More than happy to explain further if you'd like. Or is someone else has a better approach, here's the data...
Midpack,

I am overwhelmed that you did all that work. I will PM you to ask about your methodology, since it is quite beyond my level of sophistication (although I do learn quickly). My professor will accept my using an "expert consultation" if I can demonstrate that I understand how the calculations were done.
__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Old 10-12-2009, 05:23 PM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Midpack's Avatar
 
Join Date: Jan 2008
Location: Chicagoland
Posts: 11,977
Quote:
Originally Posted by Amethyst View Post
Midpack,

I am overwhelmed that you did all that work. I will PM you to ask about your methodology, since it is quite beyond my level of sophistication (although I do learn quickly). My professor will accept my using an "expert consultation" if I can demonstrate that I understand how the calculations were done.
No trouble at all, I do that kind of thing as part of my job all the time, it took about 5 minutes. I responded to your PM, let me know if I can help from there - no problem.
__________________

__________________
No one agrees with other people's opinions; they merely agree with their own opinions -- expressed by somebody else. Sydney Tremayne
Retired Jun 2011 at age 57

Target AA: 60% equity funds / 35% bond funds / 5% cash
Target WR: Approx 2.5% Approx 20% SI (secure income, SS only)
Midpack is offline   Reply With Quote
Reply

Tags
cost, excel, expense, investment property


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Odd Quirk in Excel haha Other topics 7 02-10-2009 03:20 PM
excel question simple girl Other topics 3 12-20-2008 12:16 PM
extra expenses? lowered expenses? 72t? retiringat50 FIRE and Money 6 01-08-2008 09:30 PM
How can I post an Excel file? accountingsucks FIRE and Money 4 07-10-2006 09:23 AM

 

 
All times are GMT -6. The time now is 10:42 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.