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
-
No problem! All you need to do is add the ROUND function around your Holding Cost formula, like so:
=ROUND(([Loan amount]@row * interest rate / 365), 2)
This will give you $8.63 without the expanded value in the background. Let me know if this works for you!
Cheers,
Genevieve
Answers
-
No problem! All you need to do is add the ROUND function around your Holding Cost formula, like so:
=ROUND(([Loan amount]@row * interest rate / 365), 2)
This will give you $8.63 without the expanded value in the background. Let me know if this works for you!
Cheers,
Genevieve
-
@Genevieve P that was 25,000x simpler than I thought it was going to be :-P worked perfectly!!! Thanks so much!
-
No problem at all! Glad I could help. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
