Forum Discussion

fkkimber's avatar
fkkimber
Copper Contributor
Mar 27, 2024

Loan Amortization Schedule Build

Hi community, 

 

I am trying to build a loan amortization schedule so I can look at my payments as time goes on. I've seen the template "Loan Amortization Schedule" (shown below), it doesn't quite meet all the needs that I have. I currently have to pay PMI so my "Scheduled Payment" will be different until I hit 20% of my principal. I also would like to be able to have a variable "Optional extra payments". I assume that I could enter the optional payment in the corresponding month and simply break/erase the formula. But it is the "Scheduled payment" that I am not sure how to change. Any help would be greatly appreciated.

 

Excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Mar 27, 2024

    fkkimber 

     

    Here's a link to a set of Excel's many Financial Functions, if you're at all inclined to create your own spreadsheet to meet your individual needs.

     

    Otherwise, if you want more help, you could help us help you by spelling out how PMI works in your case, what you pay for it on a monthly basis, and so on (by the way, not everybody knows what "PMI" stands for; I had to look it up) as well as any other special needs.

    • fkkimber's avatar
      fkkimber
      Copper Contributor
      Mar 29, 2024
      Understandable. PMI is Private Mortgage Insurance, basically an added cost to my monthly payment. So the template would function perfectly if I did not have to worry about PMI. But I have to pay PMI until I hit 20% of my principal loan is paid (eg. if loan was $400,000 until I had paid 20% of it ($80,000) I would have to pay PMI). So for example if my calculated monthly payment without PMI was 2000, my payment with PMI would be 2050. This $50 cost doesn't go towards principal or interest it goes to my mortgage provider. I am hoping to modify the template such that until my principal has been paid down the $80,000 my scheduled payment would be the $2050 and once that $80,000 has been hit it would shift to the $2,000. (This would be in the actual schedule not in the summary shown at the top, rather in the summary there would be another "enter value" of the cost of PMI and another "loan summary" line showing the $2,050.
      • mathetes's avatar
        mathetes
        Silver Contributor
        Mar 29, 2024

        fkkimber 

         

        I'm not finding that specific template. When I go to Microsoft's template page and enter "Loan Amortization Schedule" I come up with a host of alternatives but not a one that looks like yours. Could you provide a specific link to that one, or, better yet, post a copy of it on OneDrive or GoogleDrive with a link here that grants access.

         

        (I gather you weren't positively inclined to try to design your own using the many Financial Functions that my first post pointed you to.)