Excel TRUNC function

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.

Related Functions

Excel CEILING function

The Excel CEILING function rounds numbers up to the nearest multiple of significance, ideal for pricing and budgeting calculations.

Excel ROUNDUP function

The Excel ROUNDUP function rounds a number up, away from zero, to a specified number of digits, ideal for precautionary measures in calculations.

Excel FLOOR function

The Excel FLOOR function rounds numbers down to the nearest multiple of significance, useful for inventory and budget control.

Excel ROUNDDOWN function

The Excel ROUNDDOWN function rounds a number down, away from zero, to a specified number of digits, useful for conservative estimations.

Excel ROUND function

The Excel ROUND function rounds a number to a specified number of digits, crucial for accurate financial and scientific calculations.

Content Navigation