Payback time calculation in Excel

  Timoj 11:30 04 Oct 2003

I need to make simple model for investment calculation, where also payback time is shown. In one cell is the original investment (e.g. -100) and in the following cells income due to this investment. Annual income is not constant, but varies between the years. Calculation time can be 10-20 years.
I have only been able to make it simply by guessing a payback time (e.g. 5 years) and then using average function to calculate average income for the selected years, payback time is calculated by dividing the investment by thi average income. If the result differs from the guess, I have tio manually change the guess.
I would think that it was possible to make Excel to do this iteration automatically. It could e.g. count the number of cells participating in the average calculation and compare it to the resulting payback time (rounded up to next full year) and then add or substract one cell from the average calculation. The maximum number of cells in the average calculation should be same as the calculation time in years.

  VoG II 11:39 04 Oct 2003

You could try using Tools/Goalseek or Tools/Scenarios

Type Goalseek or Scenarios into Help to see what they do.

Excel has a range of Financial functions that may help here - I'm not familiar with most of them. (Some of them need the Analysis Toolpack to be loaded.)

  Cesar 11:48 05 Oct 2003

In all Financial calculations there are five main parameters, Period, Interest, Present Value, Future Value and Payment, One of those must be a negative value, (usually payment) it appears that you are looking for a FV scenario, but beware of using excel, see my Previous thread.
click here

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

Iconic New York graphic designer Milton Glaser on his uplifting new subway posters

New iMac Pro release date, UK price & specs rumours

Comment suivre le parcours du père Noël ?