I am trying to figure out a formula to build an amortization schedule in Excel with a 365/365 accrual base. It appears that the PMT function in Excel calculates monthly payments on a 365/360 basis. I am trying to create this so that I can plug in loan information and it will spit out the same information to match the contracts and if it doesn't, I will be able to quickly identify a problem. The function I'm currently using that I believe to be 365/360 calculation reads like this: =PMT(Interest Rate/12,Number of payments,Amount of loan). I then have a separate interest column that is calculated using daily interest (Amount of loan*interest rate/365)*number of days in the payment period. For the first payment date, it is made to be 30 days from the date of booking/funding per our promissory notes (i.e. booking date is 2/7/17 then the first payment date is 3/9/17). The remainder of the payments would fall on the 9th of every month until maturity. This column is coming out to be calculated correctly for the first payment. However, the principal balance is not correct because the total P&I payment calculation is incorrect. Please provide me a calculation/formula to do a 365/365 monthly payment calculation. I have looked everywhere!
Thanks!
Thanks!
