background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

YEARFRAC Function

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

Last Updated January 2, 2023

Learn Online Now

YEARFRAC Function

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.

dl
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)

YEARFRAC Function Formula Excel

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.

YEARFRAC Function Calculator

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.
Learn More
Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.