YEARFRAC Function

Basic Description

The Excel Yearfrac function returns the fraction of a year that is represented by the number of whole days between two supplied dates.

Syntax: YEARFRAC( start_date, end_date, [basis] )

Where the arguments are as follows:

start_date The start of the period   (this date is included in the calculation)
end_date The end of the period   (this date is included in the calculation)
[basis] An optional argument, which specifies the type of day count basis to be used

Possible values of [basis] and their meanings are:

[basis] Day Count Basis
0 (or omitted) US (NASD) 30/360
1 actual/actual
2 actual/360
3 actual/365
4 European 30/360

Note that Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the start_date and end_date arguments for the Yearfrac function should be input as either references to cells containing dates or dates returned from other functions or formulas

 

Yearfrac Function Examples

The spreadsheets below show examples of the Excel Yearfrac function used to calculate the year fraction between 1st January 2014 and 31st March 2014, using each of the different Day Count Basis types.

 Formulas:
A B C
1 Start Date: 01-Jan-2014
2 End Date: 31-Mar-2014
3
4 Yearfrac between 1st Jan & 31st Mar 2014:
5 =YEARFRAC( B1, B2 ) – US (NASD) 30/360 basis
6 =YEARFRAC( B1, B2, 1 ) – actual/actual basis
7 =YEARFRAC( B1, B2, 2 ) – actual/360 basis
8 =YEARFRAC( B1, B2, 3 ) – actual/365 basis
9 =YEARFRAC( B1, B2, 4 ) – European 30/360 basis
 Results:
A B C
1 Start Date: 01-Jan-2014
2 End Date: 31-Mar-2014
3
4 Yearfrac between 1st Jan & 31st Mar 2014:
5 0.25 – US (NASD) 30/360 basis
6 0.243835616 – actual/actual basis
7 0.247222222 – actual/360 basis
8 0.243835616 – actual/365 basis
9 0.247222222 – European 30/360 basis

 

Yearfrac Function Errors

If you get an error from the Excel Yearfrac function, this is likely to be one of the following:

#VALUE! – Occurs if the start_date or end_date arguments are not valid dates.
#NUM! – Occurs if the value of the supplied [basis] argument is less than 0 or greater than 4.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment