Amortization table using interest accrued on a 360/365 day basis.

Need guidance on what formula to use in order to calculate interest accrued for a loan using 360/365 day basis.

Thank you.
 

Question Info


Last updated November 19, 2019 Views 8,806 Applies to:
Answer
Answer
NOYAZO wrote:
Need guidance on what formula to use in order to calculate interest accrued for a loan using 360/365 day basis.

That day-count method does not make sense to me.  I wonder if you really mean 365/360, more commonly written as actual/360.  See http://en.wikipedia.org/wiki/Day_count_convention.

For actual/360, the annual rate is converted to a daily rate based on 360 days per year.  In the US, that is annualRate/360.  In the EU, I believe that is (1+annualRate)^(1/360)-1.

Then the periodic interest between Date1 and Date2 is (Date2-Date1)*dailyRate.  The accrued interest is simply the sum of the periodic interest.

Note:  I prefer not to round interest calculations.

If you truly mean 360/365, the annual rate would be converted to a daily rate based on 365 days per year:  either annualRate/365, or (1+annualRate)^(1/365)-1.

And perhaps the periodic interest would be (360/frequency)*dailyRate, where "frequency" is the number of periods per year.  For example, for monthly periods, the periodic interest would be 30*dailyRate (30 = 360/12).

(But 30/360 is more common for that purpose.  That is, the annual rate is based on 360 days per year, and each month is presumed to have 30 days.)

For a specific formula, you will need to provide more information, namely what columns and rows contain what values in the amortization table.

Preferably, you can upload an example Excel file (devoid of any private data) that demonstrates the problem to a file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here.  The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.