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