Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Hedge Fund InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open

# YEARFRAC Function

Step-by-Step Guide to Understanding the YEARFRAC Function in Excel

Last Updated January 2, 2023

## How to Use YEARFRAC Function in Excel (Step-by-Step)

In Excel, the YEARFRAC function is a built-in feature used to determine the precise difference in time between two specified dates.

Since the primary use-case of the YEARFRAC function is to count the number of full days between a given starting and ending date—the resulting figure is a decimal value, i.e. the returned value is a fraction of the given year.

For instance, if the start date is the end of a fiscal year, or Q-4 (12/31/2021), and the end date is Q-1 (03/31/2022), the YEARFRAC function returns a value of 0.25, or 25%.

Note that if either the start or end date is formatted improperly (e.g. entered as a text string) or not as a valid date, the returned value will display a “#VALUE!” error message.

## YEARFRAC Function Formula Syntax

The Excel YEARFRAC function formula is as follows.

=YEARFRAC(start_date, end_date, [basis])

Of the three arguments, only the first two are required, whereas the third is optional and can be omitted, as denoted by the brackets around “basis”.

• start_date” → The opening parameter and date from which the function determines how far out the end date is.
• “end_date” → The closing parameter representative of the ending date to reach.
• Basis” → The day count basis the function is to follow

If the “basis” input is omitted, the default day count basis convention in Excel is 30 /360 (30 day a month, 360 days a year).

• “0” → U.S. 30 /360
• “1” → Actual Days / Actual Days in Year
• “2” → Actual Days / 360
• “3” → Actual Days / 365
• “4” → European 30 / 360

## YEARFRAC Function Calculator – Excel Model Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Submitting...

## Excel YEARFRAC Function Calculation Example

Suppose we’re tasked with calculating the present value (PV) of a series of cash flows generated by a new project undertaken by a company.

The project’s cash flows, however, occur at irregular periods, which is where the use-case of the YEARFRAC function comes in.

The initial outlay incurred by the company while setting up the project was \$5 million on 12/31/2022, which we’ll assume is the present date on which the analysis is completed.

The table below shows the projected cash flows after the initial outflow (t = 0).

12/31/2022 06/30/2023 06/30/2024 02/28/2026 10/31/2027
(\$5 million) \$600k \$1.2 million \$2 million \$2.6 million

Our discount rate assumption will be 8.0% and the present value (PV) formula involves dividing the cash flow by (1 + discount rate) and raising it to the power of the period number.

To calculate the period number—i.e. the difference between the date on which the cash flow is received and the present date—we’ll use the YEARFRAC function.

For example, the formula for calculating the time period of the first cash inflow of \$600k received on 06/30/2023 is the following:

=YEARFRAC(\$D\$4,E4)

We’ll anchor the initial date cell (press F4) and copy the formula across the rest of the projection period.

• 06/30/2023 = 0.50
• 06/30/2024 = 1.50
• 02/28/2026 = 3.16
• 10/31/2027 = 4.83

For the initial two cash flows, we can see the difference from the present date is relatively standard, i.e. 6 months (half year) and 18 months (one and a half years) from the current date, respectively.

But for the latter two cash flows, the timing is more irregular in that those dates are 38 months and 58 months from the present date.

Once we plug our figures into the present value (PV) formula, we can calculate the discounted value of the project’s cash flows.

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.