Adaptive Date Relative, Month Count in Given Year!!!

EDIT: Rewriting the post to be more concise. The values do equate if you need a reference.

Irrelevant info, but maybe helps: Doing a Net Present Value on a lease term. These formulas are in Column H.

My NPV formula for year 1:

Please Login or Register  to view this content.

My NPV formula for year 2:

Please Login or Register  to view this content.

My NPV formula for year 3:

Please Login or Register  to view this content.

The D1 is the start date and D2 is the term length. E column is the current year. F column is my rent cash flow. G column is my fixed discount rate. H column is where I'll put my formula. Notice the numerator of the exponent is the number of months passed by the end of 2018, 2019, and 2020, since the start of the term, then divided by 12.

Getting past the stuff you don't really need to know about NPV formulas or lease terms, the numerator of the exponent is the part where I need help!

D	E	F	G	H
02/01/2018	2018	$24.75	.075	$23.16
123 months	2019	$27.99	.075	$24.47
	2020	$29.11	.075	$23.88
	2021	$30.27	.075	$23.40
	...	...	...	...
	2028	$13.19	.075	$7.46

The desired formula:

Please Login or Register  to view this content.

So pasting this into the H column and dragging down should net me a numerator that is adapting to the current year and how many months have passed since the start date (D1) including if I were to change D1 to a new date and/or change the term length from 123 months.

At the end of the day this looks like a lot, but I'm just providing as much info as possible. I think it'll end up being a complicated DATE formula in which is totally beyond me.

Thank you so much for taking the time to read and any suggestions are greatly appreciated!

There is a problem with your data and/or calculations. Your formulas cannot yield the values that you posted if G28 stays the same.

I figured out how to solve it!! The answer was :

Please Login or Register  to view this content.

Essentially, I needed the number of months between the current value and the start date. This was simple enough for every year except the last year. Then I realized that by making a simple IF formula that says if the current year is the same as the last year of the term, produce the term length which is already a fixed value. It seems so simple after the fact!

Now If I change any values, especially D2 & D3, it will automatically produce the correct result.


Thank you for the attempted help and I apologize if my explanation was not clear.