Per diem interest rounded to nearest cent doesn't translate to accumulated interest?

Happy Thursday!!!

I've got a sheet that tracks loans to generate statements and payoffs. For the most part, it works GREAT.

The "Holding Cost" (per diem) column is run by =[Loan amount]@row * interest rate / 365

Since the column is set for Text/Number & Currency, it automatically rounds to the nearest cent which is perfect for my needs.

The issue is that the "Accumulated interest" column, run by =[Holding cost]@row * [Days on loan]@row does not use the rounded version of the data in the Holding cost row, but the expanded decimal.

Ex: $8.63 * 419 = $3,615.97 (which is what I need shown)

But the expanded value returned for holding cost is: 8.630136986301369863013698630137 * 419 = 3,616.0273972....rounding to $3,616.03 as I've got it set to currency.

For my specific use, I need to calculate that interest amount by the rounded whole cent shown in my sheet, not the expanded decimal.

Any tips on how to do so?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!