The DAYS360 function calculates days using 30 days for each month.
When using a 365-day daily interest rate computation, the interest rate is divided by 365 and multiplied by the principal for each day you calculate interest. When using a 360-day year method, the interest rate is divided by 360 and multiplied by the principal for each day outstanding. Effectively, the 360-day method charges five more days of interest per year. This makes it disadvantageous for a borrower due to the higher cost.
Using a 6.5% $100,000 30-year mortgage as an example, a buyer would pay $129,361 in interest over the life of the loan using the 360-day method instead of paying $127,224. Accordingly, using the 360-day method creates $2,137 more interest expense.
Google Sheets’ DAYS360 function simplifies the process of calculating the number of days in a period. It assumes that each month has 30 days, regardless of the actual number of days in that month. Refer to example 3 below to understand the intricacies of this function.
Contents
Purpose
The DAYS360 function calculates the difference between two dates using the 360-day year often specified in financial contracts.
Syntax
=DAYS360(start date,end date,[method])
start date– The beginning date for the period of the calculation. Remember how dates and times work in Google Sheets when entering this value.end date– The ending date for the period of the calculation. Remember how dates and times work in Google Sheets when entering this value.[method]– OPTIONAL. US method = 1, European Method = 2. The European method changes the day of thestart dateorend dateto 30 if it is the 31st.
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Examples
Example 1 – Days in a Year
These are the same dates, with the difference calculated by two different formulas. The first is simple subtraction, and the second is the DAYS360 formula.
| A | B | |
| 1 | Start date | End date |
| 2 | 1/1/1960 | 12/31/1960 |
| Formula | Description | Result |
=B2-A2 | Regular number of calendar days between two dates | 365 |
=DAYS360(A2,B2) | Number of days between two days using the 360-day method | 360 |
The DAYS360 calculation returns 360 days for the year 1960, while subtracting the two days shows that there are 365 calendar days between them. The 5-day difference can lead to more interest charged on loans. Let’s look at that next.
Example 2 – Effect on Interest Amounts
Following is a comparison of the amount of interest charged on a one-year loan using a 360-day year versus a 365-day year.
| A | B | Formulas in B | |
| 1 | Start date | End date | |
| 2 | 1/1/1960 | 12/31/1960 | |
| 3 | Principal | $100,000 | |
| 4 | Interest rate | 6.5% | |
| 5 | Number of years | 1 | |
| 6 | Interest expense 365 method | $6,500.00 | =B3*B4 |
| 7 | Interest expense 360 method | $6,590.28 | =B3*((B4/(DAYS360(A2,B2))*365)) |
| 8 | $ Difference | $90.28 | =B7-B6 |
| 9 | % Difference | 1.39% | =B8/B6 |
The 360-day method nets the lender 1.39% more interest in this example. No wonder why a bank may use it.
Example 3 – Four Full-Year Examples
This example is on the second and third worksheets of the live Google Sheet. The second worksheet shows 365 examples of the function using December 31st as the start date and December 31st of the following year as the end date. It then shows the difference between the DAYS360 function and subtracting the two dates. On the same worksheet is the DAYS360 function using the non-US variant.
The following worksheet shows 365 examples of the function using January 1st as the start date and going through December 31st as the end date. It also shows the difference between the DAYS360 function, subtracts the two dates, and then compares it to the non-US variant. Interestingly, starting on January 1st is the only example of the two that shows a difference between the international and US methods.
Live Examples in Sheets
Go to this spreadsheet for examples of the DAYS360 function shown above, which you can study and use anywhere you would like.
Video Explanation
Related Tutorials
See the impact of the 360-day method on 30-year loan interest charges.