0

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.

spreadsheet 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

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))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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