What formula can I use to produce due dates for amortization table based on payment frequency?

Hi!

I am working to have the amortization table also auto supply the due dates for each payment if a defined cell shows Annual, semiannual, quarterly, or monthly. Currently I am using a nested If function.

Here is the formula that I have added:

=IF(ISBLANK(Period3), "", IF(Value16 = "monthly", DATE(YEAR(Date2), MONTH(Date2) + 1, DAY(Date2)), IF(Value16 = "annual", DATE(YEAR(Date2) + 1, MONTH(Date2), DAY(Date2)), IF(Value16 = "Quarterly", DATE(YEAR(Date2), MONTH(Date2) + 4, DAY(Date2)), IF(Value16 = "Semiannual", DATE(YEAR(Date2), MONTH(Date2) + 6, DAY(Date2)))))))

When selecting Annual I have no issues with it counting each year, monthly populates the first year but will not continue to the next year. When Quarterly or Semi-Annually is chosen it shows #Invaild

I believe the error is due to not counting through to the next year, but I am not sure of an alternative way to create this formula. For example, there are only 12 months in a year so once the formula is at 12/1/2028 it will not go to 13/1/2028 since there are not 13 months in a year, but it will not transition to 1/1/2029 either.

Is there a way that I can fix this?

A smaller issue, how do I get the formula to always use the same cell (in this case Value16) when I drag the formula down the column?

Any help is appreciated!

Find more posts tagged with

Accepted answers

SoS | Dan Palenchar

Hello @STA13

Here are formulas to return the date 1, 4, and 6 months in the future based on a Date in column called Date Column. Use this in your nested IF() formula:

IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, 1, DAY([Date Column]@row)))
IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 4, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 8, DAY([Date Column]@row)))
IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 6, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 6, DAY([Date Column]@row)))

This Sheet I made might help you, see the section Return the date some # of months in the future

https://app.smartsheet.com/b/publish?EQBCT=fa150bb4bb6f4847bcf1dec995aefa41

Hope this helps!

SoS | Dan Palenchar

@STA13, happy to help!

Yes, you can force a column or row to stay the same with a $. Add a $ before the Column and/or row # to keep it/them constant as either:

$Value16 > Formula will always reference Value column but row # can change
Value$16 > Formula can reference different columns but always row 16
$Value$16 > Formula will always reference the exact cell in column Value row 16

All comments

SoS | Dan Palenchar

Hello @STA13

Here are formulas to return the date 1, 4, and 6 months in the future based on a Date in column called Date Column. Use this in your nested IF() formula:

IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, 1, DAY([Date Column]@row)))
IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 4, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 8, DAY([Date Column]@row)))
IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 6, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 6, DAY([Date Column]@row)))

This Sheet I made might help you, see the section Return the date some # of months in the future

https://app.smartsheet.com/b/publish?EQBCT=fa150bb4bb6f4847bcf1dec995aefa41

Hope this helps!

STA13

@Dan Palenchar Yes this worked!

Here was my final formula:

=IF(ISBLANK(Period3), "", IF(Value16 = "monthly", IFERROR(DATE(YEAR(Date2), MONTH(Date2) + 1, DAY(Date2)), DATE(YEAR(Date2) + 1, 1, DAY(Date2))), IF(Value16 = "annual", DATE(YEAR(Date2) + 1, MONTH(Date2), DAY(Date2)), IF(Value16 = "Quarterly", IFERROR(DATE(YEAR(Date2), MONTH(Date2) + 3, DAY(Date2)), DATE(YEAR(Date2) + 1, MONTH(Date2) - 9, DAY(Date2))), IF(Value16 = "Semiannual", IFERROR(DATE(YEAR(Date2), MONTH(Date2) + 6, DAY(Date2)), DATE(YEAR(Date2) + 1, MONTH(Date2) - 6, DAY(Date2))), "")))))

Any idea how I can copy this down the column without Value16 changing?

I want a more efficient way then correcting this around 1200 times.

Thank you so much for your help so far!

SoS | Dan Palenchar

@STA13, happy to help!

Yes, you can force a column or row to stay the same with a $. Add a $ before the Column and/or row # to keep it/them constant as either:

$Value16 > Formula will always reference Value column but row # can change
Value$16 > Formula can reference different columns but always row 16
$Value$16 > Formula will always reference the exact cell in column Value row 16

STA13

@Dan Palenchar that is extremely helpful information!! Thank you!!☺️

SoS | Dan Palenchar

@STA13 Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.