Define Basis

[Janie]

I was trying to explain the YEARFRAC function to someone. I use it and
always omit the Basis. But I couldn't really explain what the different
Basis values mean. Oh, I know it determines how the days are counted .. but
exactly HOW???

The online help doesn't explain the value at all, and so I was hoping
someone could give me a good definition on how to determine which Basis one
should use under which circumstances.

Thankx for any insight.

 

[Marty S.]

Hi, Janie,

Use this Microsoft web link for the complete explanation:

http://office.microsoft.com/en-us/excel/HP052093441033.aspx

The 4 basis options cover various different accounting approaches to
breaking down accounting (e.g. 30 day accounting months / 365 days/year vs.
the actual number of days in a year, which is different on a leap year)

Hope this helps.

Regards,
Marty S.

 

[Martin Fishlock]

Marty,

Unfortunately that is the same as the help page and it does not explain the
basis for calculating the fraction of the year.

The five options are

0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

It appears that the 30/360 are based on 30 day months with 360 days a year
and the other 3 are based on actual days and different days in the year.

But it is not clear what the difference between the US and the European
methods.

 

[RichardSchollar]

Wikipedia explains the differences:

http://en.wikipedia.org/wiki/Day_count_convention

Richard

 

[Bob Phillips]

The difference is that:

U.S. (NASD) method. If the starting date is the 31st of a month, it
becomes equal to the 30th of the same month. If the ending date is the
31st of a month and the starting date is earlier than the 30th of a
month, the ending date becomes equal to the 1st of the next month;
otherwise the ending date becomes equal to the 30th of the same month.


European method. Starting dates and ending dates that occur on the
31st of a month become equal to the 30th of the same month.

Why? Who knows. It is accountancy, and we all know that is all about getting
the answer you want.

I have to ask why bother with YEARFRAC at all.
YEARFRAC, basis 1 produces non-exact years of service for all anniversary
dates except the first.


YEARFRAC, basis = 3 similarly fails to produce exact years for all
anniversary dates except the first.

It is fraught with problems IMO.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

In short, IMO yes.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Martin Fishlock]

It really depends on what you want to do with the result.

If you are working in a specific field then the yearfrac may be useful.

Date calculations are always more difficult to deal with especially with the
leap year.

But if you are working in an enviroment that the yearfrac is speciified as
the way to go then use it but just be careful of the results.