I would like the formula to figure out the effective interest rate for
a loan that includes a lump payment fee (not part of the principal) on
the last day of the last period.

The loan is amortized but with a balloon at the end.

This gets technical if you want to do it right, you need to know the
difference between rates and yields,  you have to solve a polynomial
of degree equal to the number of payments..., etc...

If you just want an answer, play with the excel IRR function. IRR is
internal rate of return, and is one measure of return-on-investment.

Pretend you are the bank and list the cash flows in a column, - for
out the customer and + for in to the bank, e.g.
-1000000 (for a million dollar loan),34567,34567,... (the monthly or
yearly payments),+balloon at the end.  Then in a new cell type =IRR(
and select the cells in the column.  You'll get an effective interest
rate. THE UNITS OF THIS INTEREST are per period.  If your rows are
months, it is %/month.   If years, %/year. Also, you may need to
adjust the number of decimal points using Format->Cell

If you are comparing balloons to a standard mortgage on the basis of
interest rate, you are could be making a mistake: often some kind of a
$ figure makes more sense than a % figure.

Good luck.

I am getting a "NAME" error.

figured it out, thanks.