Hi Everybody,


I am trying to define a function that calculates a forward price, this
should be an iterative process and unfortunately I am not able to
properly define the required loop.

The function shown below performs the required calculation but it
should be repeated until the LOAN_AMOUNT + FPA_COST equals the
SETTLEMENT_AMOUNT. I have made the calculation manually in Excel and
the price is normally found in 3 to 5 steps.

Would anyone be able to define the required loop?

Many thanks in advance!,

Paul

---------------------------------

Function FPA_price(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

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

FPA_price = Round(settlement_amount / bond_amount * 100, 6)

End Function

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.

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

On Mar 13, 4:19*am, Paul S wrote:
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- Hide quoted text -

- Show quoted text -
You may find this useful

http://www2.gsu.edu/~fncjtg/Fi8000/dnldbond.htm

On Mar 13, 2:19 am, Paul S wrote:
Thanks for your response, in the meantime I have received
some other help. The function now looks like this and it
works!

I can confirm that it converges on a result. It is unclear to me why,
and I cannot say whether or not the result is correct (the definition
of "it works"). But that is because I am not familiar with the
financial theory here.

However, I do believe there might be instances where the loop does not
terminate. That is because "loan_amt < settlement_amount + fpa_cost"
might never be true, even though it should be mathematically, due to
anomalies of binary floating-point arithmetic, which is the internal
representation of type Double.

(Admittedly, it works for the several example parameters that I
tested.)

I assume that at least "coupon" and risk_free_rate are non-integers,
and I suspect that "fraction" is also a non-integer. In any case, it
appears that fpa_cost might become a non-integer.

With type Double (binary floating-point), most non-integers (and most
integers 2^53) cannot be represented exactly. Thus, arithmetic
often results in unexpected anomalies. For example, 10.1 - 10 < 0.1
(!). Likewise, adding 0.1 repeatedly starting with 0 will never
exactly equal 10.1 (!).

Therefore, it would be prudent to implement the loop termination
condition by one of the following options:

Do While Abs(settlement_amount + fpa_cost - loan_amt) = 0.0000005

Do While Round(settlement_amount + fpa_cost - loan_amt, 6) < 0

The choice of 0.0000005 and 6 is arbitrary. I chose them based on
rounding precision used to compute FPA_test.

Aside.... It is not necessary to compute FPA_test within the loop.
There is no advantage to computing it within the loop as it is written.