Annual Percentage Rate (APR) with a Balloon Payment

[Imjustlearning]

I saw this post from sc849nm from 2010 and I would like to bring it up again to see if it can be answered. I’m in the same boat as Scott and would love to see if the APR for a balloon payment loan or Interest term loan can be found.

I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to use the RATE function – Rate(nper,pmt,pv,[fv],[type],[guess]) - but it only gives me the APR for a loan that is amortized over 30 yrs and paid in 30 years. I would like to use Excel to find the APR for a loan that is amortized for 30 years with a balloon payment in 5 years. Can anyone help?


This APR has to do with points paid on a mortgage loan. For ex. a 100,000k loan with a 1 point Fee ($1000) 30 yr loan at 6%, with a pmt of $599.55, - PMT(rate,nper,pv,(fv),type) - would have an APR of 6.094, but if you did the loan as a 30yr amortized loan with a 60 month balloon payment ($93,653.92) the APR with that 1 point would be 6.241 because of the shorter term of the loan. I use a mortgage software program so I know the APR’s are correct but I don’t know how the software arrives at the APR.

I'm hoping I can find a mortgage loan professional that is an Excel expert.

 

[Logit]

See if this will help : https://www.vertex42.com/Files/download2/themed.php?file=balloon-loan-calculator.xlsx

 

[Imjustlearning]

Thank you. That's a cool Balloon Payment Calculator. What I trying to come up with is the APR. The Government came up with the APR to help borrower's price loans because the APR takes into consideration the fees associated with the loan when it is originated. I've tried the rate function with the above example in this way =12*Rate(60,599.55,100,000,-99,000,0)*-1 which is 12*Rate(term,pmt,loan amt,amount financed,type). The APR shows as 7.433% and our Loan Software shows it as 6.241%. I actually just don't know how they are doing the calculation. Interestingly, if I use the same rate function and change the pmt to interest only ($500 a month) then the APR shows 6.232 and the loan software shows it as 6.233 which is close, but when I use it for other interest only loans the APR's can differ quite a bit.