1

How can I calculate the cost of a project over a given amount of time.

For example:

  • Solution 1: cost 30k setup and 3k/month for as long as we have the service.
  • Solution 2: cost 40k setup and 2k/month for as long as we have the service.
  • Solution 3: cost 40k setup and 5k/month for as long as we have the service + 20% for monthly service charges.

I calculated these manually (excel) over 3, 6 and 12 year periods.

So for 3 years:

  • solution 1: 30K + 3k(36) = 138k
  • solution 2: 40k + 2k(36) = 112k
  • solution 3: 40K + 2k(36) + 20% = 256k

I just changed out the number of months for each 3 year period and after the first 3 years didn't include the setup cost.

What I'm trying to learn from these calculations is which solution will be the most cost effective (leaving out other factors) over X amount of years. Solution 1 for example is cheaper than solution 3, but after how many years will solution 3 be cheaper than solution 1?

I'm not sure if this is the proper way to do this, if it's acceptable or if there's an easier, better solution.

Improve this question
4
  • Why can't you just use Excel formulas?
    – Doug B
    Commented Feb 24, 2014 at 21:01
  • Is there a specific excel formula I should use?
    – iliketolearn
    Commented Feb 25, 2014 at 14:14
  • Fill in each of the first 5 rows of Column A with incrementally increasing numbers (Cell A1 = 1, Cell A2 = 2, Cell A3 = 3, etc) to give number of years. In Column B put in your first equation into Cell B1 reading "=30000+3000*A1*12". Copy and paste this cell into Cells B2, B3, B4 and B5. In Column C put in your second equation into Cell C1 reading "=40000+2000*A1*12". Copy and paste this cell into Cells C2, C3, C4 and C5. You can then create a graph to show where one crosses the other.
    – Doug B
    Commented Feb 25, 2014 at 15:59
  • Yeah I did that. What I meant by "manual" was making up the formula logic. I wasn't sure if there's was already a function or if this is the proper way to do this.
    – iliketolearn
    Commented Feb 25, 2014 at 16:58

1 Answer 1

Reset to default
2

This is a typical problem for investment decisions. You can calculate this using current or net present values, using some expected economic lifetime. You can include a risk profile, using for instance Monte Carlo simulations, to predict the average and standard deviations of your investment.

The simple approach using current value you can solve using Excel goal seek, and then tell after how many years each solution becomes the best one.

An acceptable approach for capital goods (also for funding organisations) is to apply a net present value calculation without Monte Carlo simulations or alike. This is explained at a blog item of me on net present values for investing in real estate. Typically you will need a % to indicate the required return on money fixed in your investment. Often 10 or 15% is used for businesses.

You can extend this using simulations. You can do that manually, just by varying some input variables (such as initial cost, project runtime, yearly cost, etc.) and determining the net present value for each of the solutions. That should give you a feeling on the sensitivity of each solution for changes in the input variables.

If you need more tips, please update your question.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.