IFS() formula for all payment types

Now that you have calculated bi-weekly, semi-monthly, and monthly payments, you'll add them all together with an IFS() statement that combines all 3 cases in a single formula.

This section will also introduce the annual payments calculation; this replaces the words for the number of payments to make your formulas more compact.

Remember that:

Bi-Weekly payments have 26 payments per year.
Semi-Monthly payments have 24 payments per year.
Monthly payments have 12 payments per year.

All the start dates you'll be working within this section are at the end of the month. IFS() formulas are in the order Monthly, Bi-Weekly, Semi-Monthly. Do not use a 1=1 condition at the end of your formula.

This exercise is part of the course

Loan Amortization in Google Sheets

View Course

Exercise instructions

For rows 4-7, type in the IFS() formula that will return the expected date. You can refer to the calculation columns C:E in your formula.
For rows 10-13 type in the IFS() formula which will return the expected date. You should not refer to any cells other than the start date in column A and frequency in column B.
For rows 16-17 and 20-21 type in the IFS() formula which will return the expected date based on the annual payment frequency.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

Start Exercise