Exceljet

Quick, clean, and to the point

Formula puzzle - sum payments by year

A reader sent me an interesting formula problem this week, so thought I'd share it as a formula challenge. The problem is this:

You have a fixed monthly payment, a start date, and a given number of months. What formula can you use to sum total payments by year, based on the following worksheet:

What formula works in E5, copied across to I5?

In other words, what formula works in E5, copied across to I5, to get a sum for each year shown?

I came up with a formula myself, but I'd love to see your ideas, too. If you're interested, leave a comment with the formula you propose.

You can use the following named ranges in your formula if you like: mos (C5), amount (C6), start (C7), end (C8).

You can download the worksheet below.

Note: the Disqus comment system we use on this site may mangle or truncate your formula when it contains greater than (>) or less than (<) symbols. To prevent, you need to entity-encode the symbols: use < for >, and > for <