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: 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.

April 14, 2025 · 1 min · 128 words · Craig Heath

you could download the worksheet below.

So many great formulas!

Thanks to everyone who took the time to submit an answer.

What formula works in E5, copied across to I5?

Below are my rambling thoughts on the problem and some of the solutions below.

Note: I never clarified how month boundaries should be handled.

I was just following another worksheet as an example.

What formula works in E5, copied across to I5?

In many cases, IF is combined with OR or AND to keep formulas compact.

See formulas by , Arun, and David below.

The Excel DAYS360 function returns the number of days between two dates based on a 360-day year.

What formula works in E5, copied across to I5?

It’s a way of calculating months based on the idea that every month has 30 days.

This is my inefficient (but elegant!)

approach using the DATE function and anarray constantwith a number for each month.

What formula works in E5, copied across to I5?