Problem with date calculation

[DanBrill]

Hi,

I'm working with mortgages, trying to calculate the MaturityDate based on the SaleDate and the number of months of payments, SaleTerm.

For example, if a 240-month (20 year) mortgage closed on 1/15/2000, the first payment would be due on 2/1/2000 (and the first of each month thereafter), and the last payment should be on 1/1/2020.

To calculate this I'm using

Date(Month(SaleDate) + (((SaleTerm / 12) - Truncate( SaleTerm/12,0))*12), 1, Year(SaleDate) + Truncate( SaleTerm / 12,0))

There is some glitch in the month part of the date function. I think there must be some rounding-error issue. What it should do is find out how many years there are in the SaleTerm and subtract out what is on the left of the decimal point. What is on the right of the decimal point should be the fractional part of the year. Multiplying this by 12 should tell me how many months this is.

The problem only appears sometimes. If I use 240 as the term, it tells me that the maturity date will be 1/1/2020, which is correct. If I use 241 as the term, it still tells me that 1/1/2020 is the maturity date, which is incorrect. But if I use 242, it tells me that 3/1/2020 is the maturity date, which is correct again!

Here're are some examples

240          1/1/2020    c - correct

241          1/1/2020    x - wrong - should be 2/1/2020

242          3/1/2020    c

243          4/1/2020    c

244          4/1/2020    x - should be 5/1/2020

245          6/1/2020    c

246          7/1/2020    c

247          7/1/2020    x

248          9/1/2020    c

249         10/1/2020    c 

250         10/1/2020    x

251         12/1/2020    c

252          1/1/2021    c 

It seems that the first month of every quarter-year is wrong.

What's going on, and how can I fix it? Or is there just a much easier way of going about this that I haven't thought of?

Thanks,

Dan

[LiveOak]

Try:

Date(Round(Month(SaleDate) + (((SaleTerm / 12) - Truncate( SaleTerm/12,0))*12), 0), 1, Year(SaleDate) + Truncate( SaleTerm / 12,0))

Your calculated month numbers look like:

1.00000000000000000000

1.99999999999999000000

3.00000000000001000000

4.00000000000000000000

4.99999999999999000000

6.00000000000001000000

-5.00000000000000000000

-4.00000000000001000000

-2.99999999999999000000

-2.00000000000000000000

-1.00000000000001000000

0.00000000000000000000

1.00000000000000000000

I'm guessing FM truncated the months. It's also interesting that FM treats -5 as month 7, -4 as month 8... 0 as month 12 (all in the previous year).

-bd

[DanBrill]

Hey,

Thanks for the help. Messed around with Excel a bit because it's easier to see what's going on. Sure enough, rounding was the culprit. Months are apparently truncated, so month 1.99999999999 is still month 1.

The correct calculation is

Date(Month(SaleDate) + Round((((SaleTerm / 12) - Truncate( SaleTerm/12,0))* 12 ),0) ,1,Year(SaleDate) + Truncate( SaleTerm / 12,0))

Thanks,

Dan

[RussBaker]

Why don't you let Filemaker do the "months" work for you. Its pretty flexible in handling dates. Try this calc:

Date(Month(Sale Date)+Sale Term,1,Year(Sale Date))

[DanBrill]

Russ,

Brilliant! That's much easier. There was something about my formula that smacked me as inelegant. Your's is a gem.

Thanks,

Dan