Wrong ROI in Money Buildings spreadsheet

[jinqs]

The ROI calculations are wrong in the infamous Money Buildings spreadsheet found here:

https://docs.google.com/spreadsheet/ccc?key=0AgEHrvnoijXudHFVU2RxYUQzYXYtOURRd0d3M1hVR 2c&hl=en_US&pli=1#gid=0

Basic explanation of the current formula

The formula that it's using is:

Building Time + (Cost of Upgrade / (Old Hourly Income - New Hourly Income))

This is pretty straight forward. What it is figuring out is: How long after you start the upgrade will you have paid for its cost? Take the build time (you haven't started paying down the upgrade while you're waiting for it) and add the amount of time it will take for you to recover the money you spent upgrading the building given the incremental increase to income.

For example, if the building previously made you $10/hour and after the upgrade it makes you $20/hour, you are making $10/hour more than you were before. Suppose the upgrade took 2 hours and cost $50, the ROI with this calculation is 2 + (50/10) = 7 hours.

What's missing

This fails to take into account a substantial cost that is associated with performing the upgrade: the lost income while the upgrade is underway. In the example above, you would have made $20 in those first 2 hours if you had not upgraded the building at all, so the cost of upgrading the building is not actually just the $50 face value. It's the $50 face value plus $20 of lost income. Let me demonstrate with some basic math:

If I upgrade: I spend $50 initially, then make $0/hour for 2 hours and then $20/hour for 5 hours. In 7 hours I have 20 * 5 - 50 = $50 more than when I started.
If I don't upgrade: I spend $0, then make $10/hour for 7 hours. In 7 hours I have 10 * 7 - 0 = $70 more than when I started.

The actual ROI formula should expand out the "Cost of Upgrade" to include the lost income while the upgrade is underway:

Building Time + ((Cost of Upgrade + (Building Time * Old Hourly Income)) / (Old Hourly Income - New Hourly Income))

An actual example

In general this difference shouldn't be that big though, right? Wrong. Consider the upgrade for the Laundromat from level 5 to level 6. For simplicity I'll do the math with non-tycoon. The upgrade takes 12 hours, costs $1298 and raises the hourly income from $900 to $1320. The cost that the current formula takes into account is the flat cost of the upgrade or $1298. The cost that my formula takes into account include the $900/hour that you aren't making for 12 hours for a total of $12098. The cost of doing the upgrade is 9.32 times higher than the current formula accounts for... 9.32 times higher!!!

If we calculate the final ROI using both formulas we get 15.09 hours with the current formula and 40.80 hours with my formula. That's 2.7 times higher than the current formula accounts for.

Impact to optimal upgrade sequence

If you look at the difference this has to the theoretically optimal upgrade order when starting out, it is drastically different. However, I'm not sure how substantial the difference is once you're farther into the game.

Note: In terms of gold buildings, I included only the ice cream shop below because I don't intend on spending money, but I can get that one for free. :)

Recommended upgrade order using incorrect ROI formula:

Laundromat 2
Laundromat 3
Laundromat 4
Laundromat 5
Pizza Parlor 2
Pizza Parlor 3
Ice Cream Shop 2
Laundromat 6
Ice Cream Shop 3
Pizza Parlor 4
Ice Cream Shop 4
Laundromat 7
Gas Station 2

Recommended upgrade order using the correct ROI formula:

Laundromat 2
Laundromat 3
Laundromat 4
Pizza Parlor 2
Pizza Parlor 3
Ice Cream Shop 3
Pizza Parlor 4
Laundromat 5
Gas Station 2
Gas Station 3
Ice Cream Shop 4
Deli 2
Souvenir Store 2

[BeniBugatti]

Focusing on rapid income growth $/hr per upgrade hour will get you further along faster than focusing on ROI. That said, when cash is low, using ROI as your primary decision metric makes sense, until you can afford the next high growth upgrade.

[jinqs]

Focusing on rapid income growth $/hr per upgrade hour will get you further along faster than focusing on ROI. That said, when cash is low, using ROI as your primary decision metric makes sense, until you can afford the next high growth upgrade.
Good advice! I'll start factoring that column into my upgrade decisions. That doesn't change the drastic inaccuracy of the ROI calculations though. :)

[i need muney]

I think this is valid. Who's maintaining the doc?

[Dreno33]

Focusing on rapid income growth $/hr per upgrade hour will get you further along faster than focusing on ROI. That said, when cash is low, using ROI as your primary decision metric makes sense, until you can afford the next high growth upgrade.

+1

stole the words out of my mouth

[jinqs]

I think this is valid. Who's maintaining the doc?
It is. Bump.

[jinqs]

I think this is valid. Who's maintaining the doc?
Any chance it was Tramp Stamp?

[CounterSniper]

Any chance it was Tramp Stamp?

No........

[Tramp Stamp]

No........

You lie!

Hey dudes. I was wondering when someone would notice this. Short story is duder and I had a fight, duder won, and I said I'd change the formula because I was wrong. I meant to change to the formula jinqs said but for some reason I didn't and used TLoord's old formula, with the intention of fixing it later. Well, I quit soon afterwards. From what I'm reading through the skimming I'm doing, it seems itzakakarot (and maybe nosepicker) has Super Saiya-jined over 9000 with his own macro-enabled spreadsheets so mine is probably out of fashion, but if you're still interested send me an email either via Funzio PM or via the "Share" link in Google Docs and I'll grant you write access or even ownership of the spreadsheet.

P.S. the quotes in my signature are hilarious. I thought this is why I was banned, so I wonder why they weren't pruned.

[Ghost818]

You lie!

Hey dudes. I was wondering when someone would notice this. Short story is duder and I had a fight, duder won, and I said I'd change the formula because I was wrong. I meant to change to the formula jinqs said but for some reason I didn't and used TLoord's old formula, with the intention of fixing it later. Well, I quit soon afterwards. From what I'm reading through the skimming I'm doing, it seems itzakakarot (and maybe nosepicker) has Super Saiya-jined over 9000 with his own macro-enabled spreadsheets so mine is probably out of fashion, but if you're still interested send me an email either via Funzio PM or via the "Share" link in Google Docs and I'll grant you write access or even ownership of the spreadsheet.

P.S. the quotes in my signature are hilarious. I thought this is why I was banned, so I wonder why they weren't pruned.

Hi ._. .........

[Walter]

It's always great when you swing by, TS. Good to see you surface for a moment!

[CounterSniper]

You lie!

Hey dudes. I was wondering when someone would notice this. Short story is duder and I had a fight, duder won, and I said I'd change the formula because I was wrong. I meant to change to the formula jinqs said but for some reason I didn't and used TLoord's old formula, with the intention of fixing it later. Well, I quit soon afterwards. From what I'm reading through the skimming I'm doing, it seems itzakakarot (and maybe nosepicker) has Super Saiya-jined over 9000 with his own macro-enabled spreadsheets so mine is probably out of fashion, but if you're still interested send me an email either via Funzio PM or via the "Share" link in Google Docs and I'll grant you write access or even ownership of the spreadsheet.

P.S. the quotes in my signature are hilarious. I thought this is why I was banned, so I wonder why they weren't pruned.

Sorry, I dont lie. You said before you left that duder was gonna maintain it.

So how the hell you been TS! You missed us huh? :cool:

[Dravak]

LOL Tramp , why should they ban you over signature , they just wanted more soap :p

He now you dropped by create some more soap , cause event is boring ;)

[Tramp Stamp]

Hi ._. .........

Up yours! (Ban me again LOL)


Sorry, I dont lie. You said before you left that duder was gonna maintain it.

Actually, I was hoping someone would volunteer with a forced hedge that someone named would take up the cause. I actually don't know what's true; I haven't looked at any of the spreadsheets since I quit so maybe everything is fine.


So how the hell you been TS! You missed us huh? :cool:

Someone notified me that I wasn't banned anymore and should post something, but I'm confused since I was sure the avatar or signature is what got me banned so I dunno why I can post now.


LOL Tramp , why should they ban you over signature , they just wanted more soap :p

He now you dropped by create some more soap , cause event is boring ;)

Well I think SOAP is [edited by FUNZIO] because I think XML is [edited by FUNZIO], though I guess I should be grateful because if it weren't for plists (binary encoded XML) then I would never have a leg to stand on in this game.

[Dravak]

Sometimes I wonder if we all not play for funzio without getting paid lol .

See event stinks big time they just reused the damn dice events , screw up the rewards by letting credit card slip in.
Then watch people go for it , since dice was almost a free give away for all ..

Then you show up we are happy again lol , so they can't ban you yet LOL .
Need to draw out the soap a bit ;)

[Ghost818]

Up yours! (Ban me again LOL)

Et Tu Brute?

[BomberAl123]

I think there's a bit of confusion over some of the calculations.

First, ROI is not a time-based measurement. It is a very crude measure of an investment that takes no account of the rate of return, which is what's key for us:

ROI = (Gain from investment - Cost of investment) / Cost of investment

What is being called the ROI is actually a calculation for the payback period, but with an error. Our actual formula for the payback period is:

Payback period in hours = Cost of investment / Increase in hourly cashflow

where:

Cost of investment = Upgrade cost + Forgone income
= Upgrade cost + (Old income * Upgrade time)

Since we're measuring from the beginning of the build, not its completion, then we also need to tack on the build time, so in full:

Payback period = Upgrade time + (Upgrade cost + (Old income * Upgrade time)) / (New cashflow - Old cashflow)

So New cashflow and Old cashflow in the formula given in the first post need to be reversed. However, this still isn't a great measure, mainly as it ignores the value of all cashflows after the payback period. For example, imagine the same building with two possible upgrades, both for $10. One upgrade takes an hour and increases income by $10. The other takes two hours and increases income by $1,000. The first will have a shorter payback period although the second is clearly the better investment.

-------------------------------------------------------------------

The correct way to compare upgrades is to realise that, as you're paying a lump sum now in order to receive a stream of known cashflows in the future, you're essentially buying an investment with bond characteristics. Specifically, you're paying now to receive a series of negative cashflows, which represent the loss of earnings during the upgrade, followed by a series of positive cashflows in perpetuity when the upgrade is complete, which represent the increase in income.

Fortunately, the procedure for comparing the profitability of bonds is well-developed. This involves, for each investment (upgrade or purchase), calculating the single interest rate such that the discounted value of its cashflows is equal to its upgrade price. The upgrade that has the highest interest rate will be the best investment. The formula for doing this is as follows:

Where:

Co = Old cashflows
Cn = New cashflows
r = interest rate
Pn = Payout time (e.g. for Pawn Shop: P1 = 24, P2 = 48 etc. Note that we assume that the upgrade begins just after a payout)
T = Upgrade time

Then:

Upgrade price = - Co/((1+r)^P1) - Co/((1+r)^P2)... - Co/((1+r)^Pn)... + ((Cn-Co)/r)/((1+r)^T)

To explain:
-Co: are the old cash flows that you're missing out on
(Cn-Co)/r: is the value of the increase in cashflows in perpetuity (the standard formula for valuing a perpetuity bond)
(1+r)^Pn and (1+r)^T: are the discount rates used to reflect that the cash flows you receive now (or if negative, that you're missing out on) are more important than the ones you will receive in the future

This formula cannot be rearranged as an expression of 'r'. It can only be calculated by a computer by iteration. Using Excel to achieve this, we would: (i) Project the (negative) discounted cashflows that we miss out on by using a series of IF( ) statements that reference a description of the upgrade's characteristics [the -Co/((1+r)^Pn) series]. (ii) Calculate the (positive) discounted value of the increase in cashflows in perpetuity [((Cn-Co)/r)/((1+r)^T)]. (iii) Sum the values of the first two steps. (iv) Use Goal Seek to discover the single interest rate such that the sum of those values is equal to the upgrade price. The upgrade or build with the highest interest rate is the best investment.

Of course, this is all rather complicated, but we can simplify the process by pretending that instead of forgoing a series of the old cashflows during the upgrade, we just forgo one big cashflow during the middle of it. This big cashflow is determined by:

- Co*((T/P)/2)
or: - Co*T/2P

Where: P = Payout period (e.g. 24 for Pawn Shop)

Its discounted value is then:

- (Co*T/2P)/((1+r)^(1+T/2))

Therefore the simplified formula is:

Upgrade price = - (Co*T/2P)/((1+r)^(1+T/2)) + ((Cn-Co)/r)/((1+r)^T)

To simplify the formula further we can value the perpetuity, (Cn-Co)/r, as if it starts now (i.e. there is zero build-time), but then to increase the deduction in the middle of the upgrade time so as to reflect that not only are you not getting the old cashflow, but you're actually not getting the new cashflow for that period either. The deduction is now: ((Cn-Co)+Co)*T/2P, which simplifies to Cn*T/2P. Therefore the formula is now reduced to:

Upgrade price = (Cn-Co)/r - (Cn*T/2P)/((1+r)^(1+T/2))

Note that, unless somebody can rearrange this formula to express it as a function of 'r', the Goal Seek function on Excel would still have to be used for each calculation. Running this manually for every build or upgrade would be quite onerous, so a macro or VBA code to automate this could be downloaded from the Internet.

In addition, a few things to consider with any strategy where you choose your upgrades based on a simple formula are:

i) It assumes all money can be invested. Remember that upgrading your building is just like making an investment in a bond: cost now for future gains. If you have lots of money in the bank earning nothing, then your priority will be looking to put it to work, and so you will accept lower rates in order to invest a greater proportion of the money. If you're trying to calculate the fastest way to gain a set level of wealth, perhaps for a big purchase, then there's no alternative but to model combinations of builds and upgrades.

ii) It assumes land is always free and available. If land is fixed or exhorbitantly expensive and we are considering new builds or the prospect of demolition, then we must focus on cashflow per square, and Co and Cn should be replaced with (Co/A) and (Cn/A), where A is the building area in tiles (e.g. 2x2 = 9 for Pawn shop).

iii) It assumes that this upgrade is the final one, and ignores the fact that one upgrade is just a bridge to another that may be much more profitable.

I hope you found this interesting or useful. If you did, then please add me!

857332745