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.
この演習はコースの一部です
Google Sheetsで学ぶローン返済（アモチゼーション）
演習の手順
- For rows
4-7, type in theIFS()formula that will return the expected date. You can refer to the calculation columnsC:Ein your formula. - For rows
10-13type in theIFS()formula which will return the expected date. You should not refer to any cells other than the start date in columnAand frequency in columnB. - For rows
16-17and20-21type in theIFS()formula which will return the expected date based on the annual payment frequency.
