YEARFRAC Function

What is the YEARFRAC Function?

The YEARFRAC function in Excel calculates the difference between two dates and represents the fraction in decimal values.

While preparing the financial models, I always had this question: "I value the companies long after their year-end date. How do I project the cash flows 'after' the transaction date until the end of the next fiscal year?

I wasn't an Excel Geek back then, so I would pull out the calculator or visit those sketchy websites with tons of ads that gave the difference between the dates in terms of fractions.

The times have changed now, though. If the world order can change as American Investor Ray Dalio proposed, so can I.

This is the story about the YEARFRAC function and how it completely changed my approach to returning the difference between the dates as decimal numbers.

Let's see what the YEARFRAC function is, how to use the function along with a couple of examples.

YEARFRAC function Formula

The YEARFRAC is categorized as a Date and Time function that returns the difference between two dates as a decimal value.

For example, if you have two dates separated by 365 days, the result would equal one.

The function works on a simple principle - Find the difference between the two dates and divide the result by 365 days or the days in a single year.

The decimal number returned is the fractional representation of the day difference between the given dates.

The syntax for the function is:

=YEARFRAC(start_date, end_date, [basis])

where.

• start_date - (required) the starting date for the given period
• end_date - (required) the ending date for the given period
• basis - (optional) day count basis, where 0 equals the default value.

The basis argument can only accept five different values as below:

How to use the YEARFRAC Function in Excel?

By this section, you already know what you need to do if you have two different dates in the dataset.

Suppose you have the data, as illustrated below:

All you need to do is begin with an equal sign in cell D3, type in the function name, and finally reference both dates. The formula will be =YEARFRAC(B3, C3), which gives the result 0.688889 in cell D3.

By dragging down the formula till cell D7, we get

If you feel you are getting too many digits after the decimal, you can shift the decimal point toward the right, which will finally give the difference between the dates in fractions as

Since we have ignored the basic argument, Excel assumes the US(NASD) 30/ 360 convention meaning 30 days each month for a year consisting of 360 days.

This US(NASD) 30/ 360 convention was initially defined by the Financial Industry Regulatory Authority (FINRA).

Applied Example on how to use the YEARFRAC Function

You will most likely use the YEARFRAC function while preparing a company's Discounted Cash Flow Model. However, there are other use cases for the function, such as finding a person's age, assets, etc.

We can also use the function along with the IF statements, which will return two different results for the boolean values.

a) Discounting the Cash Flows to Present Value

Suppose that you prepare the company's three-statement financial model and the discounted cash flow model to calculate the stock price of the company.

Let's say you want to purchase the stock today, i.e., 16th November 2022. However, the company's financial year-end date is on 31st March.

We see that the cash flow to the firm in 2023 equals $1444.0 million. However, we do need to remember that this forecasted number is for the financial period from 31st March 2022 to 31st March 2023.

Since we will be purchasing the stock on 16th November 2022, we will only focus on the FCFF after this date.

Here we could use the YEARFRAC function to calculate the difference between today's date and the year-end date. The multiple returns can be used to calculate the FCFF after the purchase of the stock till the fiscal year-end date.

The formula will be =YEARFRAC(B6, C6) in cell C7. We get the fractional value as 0.375 for 2023, which equals 1 for 2024 and 2025.

Thus, the cash flows based on the transaction will be:

Finally, the adjusted cashflows can be used to calculate the stock price and determine whether it's undervalued or overvalued per its market price.

b) Finding the age of a person/asset

Let's assume that the bank wants to know the age of all applicants looking to borrow from the bank.

Due to its strict policies, the bank usually does not lend to anyone over 65. Suppose you have the borrower's birthdate, as illustrated below:

Here, we will use the formula =INT(YEARFRAC(C3,TODAY())) in cell D3 and drag it down till cell D12, which gives the result:

The TODAY function gives today's date, which the YEARFRAC function uses to find the fraction between both dates. Initially, the result we would get using these functions would be 59.49167 in cell D3.

However, after using the INT function, which returns the integer value for a number, we finally get the result as 59, which is the age for Gustavo Carson.

If the YEARFRAC function did not exist, we could also alternatively use the formula =YEAR(TODAY())-YEAR(C3), which will give us the same result for the age.

Ensure you change the format from 'date' to 'number' after you use the formula, or else you might get misleading results!

Other uses of the YEARFRAC function

There are other scenarios where the function can be used independently or in combination with some different process. If you are working on some project and need to track the time remaining till the deadline in terms of percentage, you can use a mix of YEARFRAC and TODAY functions.

The versatile IF statements allow you to return two results based on the fulfilled condition. You can leverage the function's ability and combine it with YEARFRAC to return customized results.

a. Calculating the percentage of the year completed

Let's say that your clients allocate you a couple of projects which need to be completed on the deadlines given to you.

Suppose that the projects' start date and end date are, as illustrated below:

Firstly, we used the TODAY function in cell C3 and then added the days to the project to get the next consecutive start dates. For example, TODAY()+5 gives the start date of Project B as 21st November 2022.

We know that TODAY is a highly volatile function whose value will change if you open the file the next day. This way, the rest of your table's values automatically get updated.

Next, we will calculate the difference between the dates as the fraction using the formula =YEARFRAC(C3,D3), which gives the value in column E as

We will create another column and paste special values in range E3:E7 as:

Finally, to get the time remaining on the project, we will use the formula =E3/F3, which gives the time remaining on projects as follows:

Suppose today's date is 11th March 2023; then the time remaining on the given projects will be:

b. Using along with the IF Statements

The IF statements shouldn't need any introductions. Even beginner Excel users know what the IF function does and how useful it has been since immemorial.

In the previous example, we did find the borrowers' age, but that does not provide any meaningful insights as to whether to lend the loans to the borrowers.

In this case, we can use the IF statements to input a condition and assign two different customized text strings to the formula based on the result of that particular condition.

Suppose the data is as illustrated below:

We will use the formula =IF(INT(YEARFRAC(C3, TODAY()))>65, "Loan cannot be offered", "Offer Loan") in cell D3 and drag it down till cell C12, which gives the result:

We knew that the bank does not offer loans to borrowers above the age of 65 and has input it as a condition in the IF statements.

This way, whenever the borrower's age is above 65, we get the result' loan cannot be offered as a text string.

You can even input a formula that will calculate the loan amount for the borrower based on age. For example, if the period is near 65, the procedure returns a smaller loan amount, whereas a generation away from 65 will return an enormous loan amount for the borrower.

If the IF statements confuse you, another alternative you can use is the Conditional Formatting Tool.

First, select the range D3:D12 and then click on the Conditional Formatting Tool or press the Alt + H + L + N.

We will format only the cells whose cell value is less than 65, i.e., the age limit for the loan.

Once we click on Ok, we get the result as

You can then add a filter to the table and sort the data according to the cell fill, which finally gives you all the borrowers who apply for the loan as

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• TRUE Function
• TRUNC Function
• UPPER Function
• VAR.P Function
• WORKDAY Function