How to use the YEARFRAC function

What is the YEARFRAC function?

The YEARFRAC function returns the fraction of the year based on the number of whole days between a start date and an end date. This is made possible because of how Excel handle dates as integers.

In Excel, date values are stored as integer numbers where each integer represents the number of days since a reference date (usually January 1, 1900). This allows Excel to perform mathematical operations on date values such as calculating the number of days between two dates. The integer representation of dates makes it easy to manipulate and calculate with date information within Excel formulas and functions.

1. Introduction

What is an Excel date?

An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.

Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is a year?

A period of 365 days (366 days in leap years) based on the revolution of the Earth around the Sun. It represents one complete cycle of seasons - spring, summer, autumn, winter. Calendar years are numbered and divided into 12 months.

What is a leap year?

A leap year is a 366 day year occurring every 4 years to maintain calendar accuracy. A year containing an extra day, February 29th, that occurs once every 4 years.
The leap year was added to the Gregorian calendar to keep it aligned with the astronomical year. It occurs in years divisible by 4, except centurial years not divisible by 400.

What is a fraction?

A fraction has a numerator and a denominator:

• Numerator - The top number in a fraction.
• Denominator - The bottom number.

For example:

6/7

• The numerator is 6, meaning 6 parts.
• The denominator is 7, meaning the whole was split into 7 equal parts.

2. Syntax

YEARFRAC(start_date, end_date, [basis])

start_date	Required.
end_date	Required.
[basis]	Optional. Day count basis. 0 - Default value. US (NASD) 30/360 1 - Actual/actual 2 - Actual/360 3 - Actual/365 4 - European 30/360

3. Example 1

Theses examples demonstrate how to calculate the fraction of a year based on two specific dates located in cell range B3:C9. The [basis] argument is 0 (zero) to 4  in this example.

Formula in cell F3:

Arguments:

• Start date: 1/1/2028 (cell B3)
• End date: 5/31/2028 (cell C3)
• Basis: 0 (cell D3, which represents the US (NASD) 30/360 method)

Result: 0.416666667 (5 months out of 12 in a year) 5/12 = 0.416666667

Formula in cell F4:

Arguments:

• Start date: 1/1/2028 (cell B4)
• End date: 5/31/2028 (cell C4)
• Basis: 1 (cell D4, which represents the Actual/actual method)

Result: 0.412568306 (accounts for leap year in 2028)

Formula in cell F5:

Arguments:

Start date: 1/1/2028 (cell B5)
End date: 5/31/2028 (cell C5)
Basis: 2 (cell D5, which represents the Actual/360 method)

Result: 0.419444444 (151 days / 360 days)

Formula in cell F6:

Arguments:

Start date: 1/1/2028 (cell B6)
End date: 5/31/2028 (cell C6)
Basis: 3 (cell D6, which represents the Actual/365 method)

Result: 0.413698630 (151 days / 365 days)

Formula in cell F7:

Arguments:

Start date: 1/1/2028 (cell B7)
End date: 5/31/2028 (cell C7)
Basis: 4 (cell D7, which represents the European 30/360 method)

Result: 0.413888889 (149 days / 360 days)

Formula in cell F8:

Arguments:

Start date: 4/1/2028 (cell B8)
End date: 5/1/2028 (cell C8)
Basis: 0 (cell D8, which represents the US (NASD) 30/360 method)

Result: 0.083333333 (1 month out of 12 in a year)

Formula in cell F9:

Arguments:

Start date: 4/1/2028 (cell B9)
End date: 5/1/2028 (cell C9)
Basis: 3 (cell D9, which represents the Actual/365 method)

Result: 0.082191781 (30 days / 365 days)

Each formula calculates the fraction of a year between the start and end dates using different basis methods, which affect how days and years are counted. The results vary slightly due to these different calculation methods.

4. Example 2

Bond valuation with accrued interest: A 5-year bond with a face value of $100,000 and a 4% annual coupon rate (paid semi-annually) was issued on January 15, 2024. If an investor purchases this bond on April 30, 2024, what is the accrued interest?

Given in the question:

• 5-year bond with $100,000 face value
• 4% annual coupon rate, paid semi-annually.
• Issued on January 15, 2024
• Purchased on April 30, 2024

Formula in cell C7:

The formula returns $583.33 which is the accrued interest between 1/15/2024 and 4/30/2024 based in a 5-year bond with a face value of $100,000.

Let's break down the formula calculation:

1. Determine the semi-annual coupon payment.
   Semi-annual coupon rate = 4% / 2 = 2%
   Semi-annual coupon payment = $100,000 × 2% = $2,000
2. Calculate the fraction of the coupon period that has passed using YEARFRAC
   First coupon date: July 15, 2024 (6 months after issuance)
   YEARFRAC(January 15, 2024, April 30, 2024, 0) = 0.2877698 (using actual/actual basis)
3. Calculate the accrued interest.
   Accrued interest = Semi-annual coupon payment × Fraction of period passed
   Accrued interest = $2,000 × 0.2877698 = $575.54

This calculation demonstrates how the YEARFRAC function is useful in determining the precise fraction of time for which interest has accrued. The function accounts for the exact number of days between the last coupon date (or issuance date in this case) and the purchase date, considering factors like leap years.

5. Example 3

Project cost allocation with varying rates: A consulting project runs from March 1, 2024, to November 30, 2024. The consultant charges $100/hour for the first third of the project duration and $150/hour for the remaining two thirds? Assuming a 40-hour work week, what is the total cost of the project?

Formula in cell C7:

The formula returns the total cost of the consulting project which is $207,229.63

1. Calculate the total project duration using YEARFRAC (this remains the same)
   Project start: March 1, 2024 Project end: November 30, 2024 Total duration = YEARFRAC("3/1/2024", "11/30/2024", 1) = 0.7472 years (approximately)
2. Calculate the number of working hours for each portion of the project
   Total working days = 0.75 years * 52 weeks/year * 5 working days/week = 195 working days Hours for first third = (195 days / 3) * 8 hours/day = 518.07 hours Hours for remaining two-thirds = 195 days * 8 hours/day - 520 hours = 1,036.148 hours
3. Calculate the cost for each portion of the project
   First third (at $100/hour): 518.07 * $100/hour = $51,807.41 Remaining two-thirds (at $150/hour): 1,040 hours * $150/hour = $155,422.22
4. Calculate the total project cost

Total cost = First third cost + Remaining two-thirds cost Total cost = $51,807.41 + $155,422.22 = $207,229.63 The total cost of the consulting project is $207,229.63

6. YEARFRAC function tips

Use the DATE function to create a date if you enter the date in the function.

Example:

The YEARFRAC function requires Excel dates to work properly, the DATE function allows you to create Excel dates by specifying the year, month, and day.

7. Function not working

If using the US (NASD) 30/360 basis which is argument 0 (zero), and the start_date argument is the last day in February the YEARFRAC function may return an incorrect result.

The YEARFRAC function may return an incorrect result if dates are entered as text values.

YEARFRAC returns

• the #VALUE! error value if start_date or end_date arguments are not valid dates.
• the #NUM! error value if basis < 0 or if basis > 4

Back to top

'YEARFRAC' function examples

The following article has a formula that contains the YEARFRAC function.

Functions in 'Date and Time' category

The YEARFRAC function function is one of 22 functions in the 'Date and Time' category.

Excel function categories

Excel categories

Leave a Comment