# Google Sheets / Excel : Opportunity cost from a set of cash outflows

Suppose I have a set of cash outflows at the end of each year. I want to compute the cumulative opportunity cost of the cash outflows each year. In the example above, suppose investments return 4% per year.

At the end of year 1 I pay out \$100.

At the end of year 2 I pay out an additional \$200. The opportunity cost so far is the return I would have made if I had kept \$100 in investments for year 2.

At the end of year 3 I pay out an additional \$300. The opportunity cost so far is the return I would have made if I had not paid \$100 in year 1 and not paid \$200 in year 2.

Is there some way I can express this in a formula that can be autofilled in Google Sheets?

Context: I'm building a calculator to look at the relative costs of buying vs renting a home.

## 1 Answer

In excel you can use following formula starting at `C7`:

``````=SUMPRODUCT((\$B\$5:B5)*(((1+\$B\$2)^ABS(\$B\$4:B4-MAX(\$B\$4:B4)-1))-1))
`````` 