On 12 mrt, 23:01, joeu2004 wrote:
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.

Thanks for your response, in the meantime I have received some other
help. The function now looks like this and it works!

Function FPA_test(fraction, loan_amount, spot_price, coupon,
risk_free_rate, commision)
Dim bond_amount As Double
Dim fpa_cost As Double
Dim settlement_amount As Double

loan_amt = loan_amount

Do While loan_amt < settlement_amount + fpa_cost

bond_amount = Round(loan_amt / (spot_price / 100), 0)
fpa_cost = (bond_amount * fraction * coupon / 4) - (loan_amt *
fraction * risk_free_rate / 4) + commision
settlement_amount = loan_amt - fpa_cost
FPA_test = Round(settlement_amount / bond_amount * 100, 6)
loan_amt = loan_amount + fpa_cost

Loop

End Function