If you want to use a non-iterative macro approach - you can use MATH. To figure out your daily period interest rate you'd use LOG(1+[required_return]) - this is key because the required return is what you want the compounded rate to be - not the APR/quoted rate. The actual APR equivalent will be lower because of MATH.
Next you can isolate your date of issuance and use a multi-row formula to calculate the number of days since issuance. You can throw on the date of your baloon payment via a union tool.
Now you are aready to start discounting - I used pow((1+([required_return]/365)),[DateDif]) - which looks at 1/365th of your required annual rate (which is basically what your compounding to).
Now we can take the discounted values via the discounting terms and sum them. We have the discounting rate for our last value and we have the total gap which that last value has to be discounted to. Working backwards we can find the remaining value by multiplying the discount rate * the negative gap amount. I got
vs
7813653
and an XIRR of
