Summary
The Excel TRUNC function truncates a number to an integer by removing the fractional part of the number. It’s used to shorten a number to a specified number of decimal places without rounding.
Syntax
=TRUNC(number, [num_digits])
- number: The number to be truncated
- [num_digits]: [Optional] The number of decimal places to truncate to. If omitted, it defaults to 0
Return value
The truncated number.
How to use
TRUNC is typically used in scenarios where you need to remove the fractional part of a number without rounding. It’s useful in financial calculations, data analysis, and whenever precise control over decimal places is required.
Examples
Simple TRUNC
Truncating a Decimal Number to an Integer: Removing the decimal part of a number:
=TRUNC(8.9)
This formula returns 8, truncating the .9 part of 8.9.
TRUNC with Specified Decimal Places
Truncating to a Specific Number of Decimal Places: Shortening a number to two decimal places:
=TRUNC(3.14159, 2)
This formula returns 3.14, truncating after two decimal places.
TRUNC in Financial Calculations
Calculating Truncated Daily Interest: Truncating interest calculated for a financial model:
=TRUNC((Principal * Rate/365), 2)
For a given principal and rate, this formula calculates daily interest truncated to two decimal places.
TRUNC vs ROUND
Comparison with ROUND Function: Highlighting the difference between TRUNC and ROUND:
=TRUNC(2.678, 1) //returns 2.6
=ROUND(2.678, 1) //returns 2.7
TRUNC returns 2.6, whereas ROUND returns 2.7.
Additional Notes
- TRUNC is different from ROUND; it simply cuts off decimal places without rounding.
- It works on both positive and negative numbers.
- TRUNC does not round numbers; it only removes the fractional part.