Future Date Calculation

[NamssoB]

I need to display the MONTH and YEAR of a future date. I'm using NPER in a calculation that returns what appears to be a LONG value (in months), and I want to add that to a specific date. I can easily figure out how to turn 5.832412335 Years into a nice rounded/truncated number = 70 months. But now I'm having trouble adding those 70 months to a date to get a future date.

For example:

1/1/2017 plus 5.832412335 years = MMM-YYYY

I don't care about the exact day, and I'm using =ROUNDUP() to get the following month, but I don't want to round the years up).


Any takers?

 

[mrhstn]

Hi

Excel dates are calculated as one day being equal to 1. If you have 5.8... years then to get a rough calculation multiply by 365 and add this to your start date.

01/01/2017 + 5.832412335 years or approximately 2128.830502275 days will give you Oct-2022.

HTH

 

[NamssoB]

SOLVED.

Might not be the most efficient method, but what I did was the following:

=DATE(YEAR(A57)+TRUNC(NPER(E57,0,-C57,F57)),MONTH(A57)+ROUNDUP((NPER(E57,0,-C57,F57)-TRUNC(NPER(E57,0,-C57,F57),0))*12,0),DAY(A57))

The above code does the following:
- Uses TRUNC() to add only the YEAR portion of the result of NPER to Year()
- Uses ROUNDUP() to convert the decimal portion of the NPER result to a month count, and add it to Month()

 

[NamssoB]

Hi

Excel dates are calculated as one day being equal to 1. If you have 5.8... years then to get a rough calculation multiply by 365 and add this to your start date.

01/01/2017 + 5.832412335 years or approximately 2128.830502275 days will give you Oct-2022.

HTH

Aah - I did not know about the *365 multiplier! That would have helped me a little, although I will would have needed some rounding.

So using that, my formula ended up being MUCH simpler:

=A57+(NPER(E57,0,-C57,F57)*365)

THANK YOU!

 

[StephenCrump]

I can easily figure out how to turn 5.832412335 Years into a nice rounded/truncated number = 70 months. But now I'm having trouble adding those 70 months to a date to get a future date.

You might also want to check out Excel's EDATE(start_date, months) function. (Noting how it works for short months, e.g. =EDATE(DATEVALUE("31 Jan 2017"),1) is 28 Feb 2017)

It looks like you are providing an annual interest rate to the NPER function. If this is 6% say, then effectively this means you are assuming 1.06^(1/12)-1 = 0.49% per month.

If instead you want to use 6%/12 = 0.5% per month, or to calculate interest on a daily balance method allowing for the different length months, then your future date will only be approximate.