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.

Diese Übung ist Teil des Kurses

Loan Amortization in Google Sheets

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Setze die Theorie in einer unserer interaktiven Übungen in die Praxis um

Übung starten