DAYS360 Function - Google Sheets
When using a 365 day year daily interest rate computation, the interest rate is divided by 365 and multiplied by the principal for each day for which interest is calculated. When using a 360 day year method, the interest rate is divided by 360 and multiplied by the principal for each day outstanding. Effectively, this charges 5 more days of interest per year. For a borrower, the 360 day year method is disadvantageous 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 as opposed to paying $127,224. Accordingly, using the 360 day method creates $2,137 more interest expense.
Sheets has a built in function for this called DAYS360. It only does part of the work for you as it returns the number of days in a period by counting each month as 30 days no matter how many days are in it. See example 3 below to see the nuances behind this.
The DAYS360 function is for calculating the difference between two dates using the 360 day year often specified in financial contracts.
=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 you enter this value.
end date- The beginning date for the period of the calculation. Remember how dates and times work in Google Sheets when you enter this value.
[method]- OPTIONAL. US method = 1, European Method = 2. The European method changes the day of the month from 31 to 30 if either of the dates occurs on the 31st.
Example 1 - Days in a year
These are the same dates with the difference between them calculated by two different formulas. The first is simple subtraction and the second is the DAYS360 formula.
|1||Start date||End date|
|Regular number of calendar days between two dates||365|
|Number of days between two days using the 360 day method||360|
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|
|5||Number of years||1|
|6||Interest expense 365 method||$6,500.00|
|7||Interest expense 360 method||$6,590.28|
Example 3 - Four full year examples
The third example is shown 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 going through December 31st of the next year as the end date. It then shows the difference between the DAYS360 function and simply subtracting the two dates. On the same worksheet is the DAYS360 function using the non-US variant.
The next 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 and simply subtracting the two dates and then compares it to the non-US variant. Interestingly, this example starting at 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 the examples of the DAYS360 function shown above that you can study and use anywhere you would like.