XL Payoff Formula?

Sorry, my last question was vague. What I have to calculate is the payoff for dozens of different loans, each starting on a different date, each with interest compounding only ONCE annually, each with a different interest rate, and all with the same payoff date. I'm wondering if there's a one step formula in XL for this.

1 Answer

  • 1 decade ago
    Favorite Answer

    Will there be interim payments, or just a lump sum at the payoff date?

    If there is just a lump sum at the payoff date, and the period is in whole years, with interest compounding at the end of each year, the forumula would be


    So for a 1,000 loan with a balloon payoff in 5 years with interest compounding anually at 5%, the formula would be: =1000*(1.05)^5

    Since you need to do this many times, put 1000 in cell a1, 5 in cell b1, and 5% in cell c1. Then put "=a1*(1+c1)^b1" in cell d1. Then you can copy cells a1 to d1 down and change the values in a,b, and c.

    If there will be annual payments, you can use the "FV" formula in Excel. Click on "Help" and type "FV" and it will tell you how to use this formula.

    Of you can just make up the payoff amounts and see if anyone notices.

Still have questions? Get your answers by asking now.