Duncan's Diurnal Diatribe: Payment Schedule: two approaches

Introduction

Payments Schedule: how to program them in two different ways

Many thanks to the originator of the example I discuss in this file but I completely forgot to make a note of his YouTube video The purpose of this file is to share with you a single cell solution approach to the creation of a Payment Schedule

On the face of it, there is nothing wrong with such an approach as it works and is probably very efficient. On the other hand, the formula needed to make the schedule work is relatively long and complex. That means it is an error prone formula, potentially difficult to understand and maintain

I present an alternative approach in which the formula required to create the payment schedule is a lot smaller and simpler. More than that, the schedule is very granular in its detail

And I use a Pivot Table to create the final, annual schedule and that is very easy to construct

Mega Formula

If we can consider the single cell solution to be a mega formula, this is how chatGPT describes it:

This Excel formula is a nested IF statement combined with AND logical conditions. It is used to perform different calculations based on various criteria. Let's break it down step by step:

First IF Condition:

=IF($C5<G$3,0, ...)

This checks if the value in cell C5 is less than the value in cell G3.

If true, the formula returns 0.

If false, it proceeds to the next IF condition.

Second IF Condition:

IF($B5>G$2,0, ...)

This checks if the value in cell B5 is greater than the value in cell G2.

If true, the formula returns 0.

If false, it proceeds to the next set of conditions.

Third Set of Conditions:

Using AND and IF:

IF(AND($C5<G$2,$B5<=G$3),$F5*($C5-G$3+1), ...)

This checks if C5 is less than G2 AND B5 is less than or equal to G3.

If both conditions are true, it calculates $F5*($C5-G$3+1).

Fourth Set of Conditions:

IF(AND($C5<G$2,$B5>G$3),$F5*($C5-$B5+1), ...)

This checks if C5 is less than G2 AND B5 is greater than G3.

If true, it calculates $F5*($C5-$B5+1).

Fifth Set of Conditions:

IF(AND($B5>G$3,$C5>=G$2),$F5*(G$2-$B5+1), ...)

This checks if B5 is greater than G3 AND C5 is greater than or equal to G2.

If true, it calculates $F5*(G$2-$B5+1).

Final Else Condition:

If none of the above conditions are met, the formula calculates $F5*G$1.

Each IF and AND statement checks specific conditions and performs a calculation based on the values in the cells referenced (B5, C5, F5, G1, G2, G3). The use of absolute references (like $C5) ensures that certain cell references do not change when the formula is copied to other cells. The exact purpose of the formula would depend on the context of the data in these cells.

Alternative Approach

As you watch my video and work with my Excel file, you will see my alternative approach is much simpler, less error prone and much easier to understand and work with. The formula I use is described by chatGPT in this way:

The Excel formula in cell B4 uses an IF statement combined with an AND logical function. Here's the breakdown of what this formula does:

AND Function:

AND($A4>=rev_rec!$B$5, $A4<=rev_rec!C$5)

This part of the formula checks two conditions simultaneously:

Whether the value in cell A4 is greater than or equal to the value in cell B5 on the rev_rec sheet ($A4>=rev_rec!$B$5).

Whether the value in cell A4 is less than or equal to the value in cell C5 on the rev_rec sheet ($A4<=rev_rec!C$5).

The AND function returns TRUE if both conditions are met, and FALSE otherwise.

IF Function:

IF(AND(...), rev_rec!$F$5, "")

This part evaluates the result of the AND function.

If the AND function returns TRUE (meaning both conditions about the value in cell A4 are met), then the formula returns the value in cell F5 on the rev_rec sheet (rev_rec!$F$5).

If the AND function returns FALSE (meaning either one or both conditions are not met), then the formula returns an empty string ("").

In summary, the formula in cell B4 checks if the value in cell A4 falls within a specific range defined by cells B5 and C5 on the rev_rec sheet. If it does, B4 will display the value from rev_rec!$F$5; if not, B4 will remain blank. This kind of formula is often used for conditional data retrieval or display based on a specific range or criteria.

Payment Schedule: two approaches

Introduction

Mega Formula

Alternative Approach

No comments: