• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inquiry: Creating an Amort. Schedule w/ Compounded Interest & Interet only quarterly PMTs

YPJunk

Member
Hello!

I am having difficulty creating an amortization schedule due to the compounded interest. The details are below and attached. Can anyone assist?

Principal: $4,063,379
Annual Interest Rate: 8% (based on 360-day year)
Compounded: Monthly
Payments: Quarterly interest payments only until maturity
Loan Disbursement Date: 10/30/2012
First Quarterly Payment: 01/01/2013
Maturity Date: 12/31/2029

I appreciate all your help!

Sincerely,

YP
 

Attachments

  • Inquiry - Amortization.xlsx
    17.5 KB · Views: 4
Last edited:
Hello YP,
To calculate the interest in this scenario, it is incorrect to take the interest rate divided by 360 and then multiply by days. That is the main reason your numbers were off. XL has a built-in function called FV which lets us calculate Future Value of an investment. I used this function to calculate what the loan would be at after so many months, and then subtract the previous value of loan to see how much interest was earned.

The other curious thing is the slight nuance of how the loan starts on the 30th of a month, but begins payment on the 1st. I made the assumption that, like many lending agencies, interest gets calculated on 1st of month. So even though the first payment was only 61 days after maturity, it was the 3rd payment period, and so we need to adjust for that.

BTW, and just out of curiosity, who would want a loan like this? End up paying over $5M just in interest, which is more than original loan! :eek:
 

Attachments

  • Inquiry - Amortization LM.xlsx
    19 KB · Views: 8
Thank you Luke! I appreciate you help. And no I do not understand why they made this financial decision. Sigh.

-YP
 
Back
Top