Loan Payment calculation with actual/360 vs 30/360 method

[g3lutz]

Hello,

I did a fair amount of googling and searching on this site before posting, so if the answer is easily found through one of those methods please post how you found it.

Has anyone wrote a UDF or VBA piece that calculates a payment amount using an actual/360 interest crediting method instead of the generic 30/360 method? If yes, please point me in the rigt directions.

Thanks,
G3

 

[RalphA]

Your question may have many interpretations. Trying to use what i believe is your basic concern, for the interest generated by daily compounding, for the 28 days from Feb 2 to Mar 2 of the same year, try:
For:
5% (interest rate, calculated daily, 365 days per non-leap year) in A3
$10000 (amount invested) in B3
02/02/06 (starting date) in C3
03/02/06 (today's date, or ending date) in D3
one can find the interest for the 28 days by using the formula, say, in E3:
=B3*(1+A3/365)^(D3-C3)-B3, which should give 38.43.

 

[g3lutz]

Clarification

I think I need to restate my question.

Based on a payment calculated using the PMT function, an original balance, a payment day, amortization begin date (to account for an IO period at the beginning of the loan only), amortization end date, and loan pay off date. I need the payoff amount based on an interst accrual method of actual/360 through the life of the loan.

Currently we are using an amort table, vlookup, and then a copy paste method to extract this information. Not overly efficient.

I am pretty sure I can code an excel function to do this, but if someone already has it and is willing to share, that would be great.

G3

 

[RalphA]

G3, I think you also have to establish how often the payments are to occur.

It seems as if you want them to take place on the same certain day of each month, say the third of each month. Then, you want to pay a monthly amount that is propotional to the number of days from the previous payment. In other words, you want to establish, practically speaking, a fixed amount per day, so that you will pay that amount times the number of days between payments.

Also, you seem to want to use 360 days for a year. I believe that, if you are going to use the actual days between dates, then you must use actual days in each year, which are 365 for non-leap years, and 366 for leap years.

As I see your need, I believe that, unless you post some numeric examples of what you need, for, say, the first four or five months, with the amounts worked out, I, for one, will not try any more to come up with a solution to a problem that is still very unclear as to how various of its components are to be calculated.