On Mar 12, 10:39*am, Paul S wrote:
I have made the calculation
manually in Excel and the price is normally found in 3 to
5 steps.

I am not familiar with this financial calculation. It would help if
you show us the Excel formulas that work for you.

Paul wrote:
The function shown below performs the required calculation
but it should be repeated until the LOAN_AMOUNT + FPA_COST
equals the SETTLEMENT_AMOUNT.
Would anyone be able to define the required loop?

I presume the structure of the loop should look like this:

Do
bond_amount = Round(loan_amount / (spot_price / 100), 0)
fpa_cost = (bond_amount * fraction * coupon / 4) _
- (loan_amount * fraction * risk_free_rate / 4) _
+ commision
settlement_amount = loan_amount - fpa_cost
Loop Until loan_amount + fpa_cost = settlement_amount

But I think that is nonsensical for several reasons.

1. You set settlement_amount = loan_amount - fpa_cost. So the "until"
condition would be true only when fpa_cost is zero.

2. Assuming your last assignment statement has a typo and you meant to
write settlement_amount = loan_amount + fpa_cost, then the "until"
condition would always be true after the first iteration. I'm sure
that is not your intent.

3. In order for an iteration to make sense, something must change
within the loop. But nothing does. You compute fpa_cost, but it is
based on invariant values -- values that do not change within the
loop.

I suspect that the function as written does not do the required
calculations after all.

Again, if you show us the Excel formulas, which you say did work in 3
to 5 steps, we might be able to help with the VBA design.