Iterative process in a VBA function

**Paul Schmitz** · 03-12-2011, 02:59 PM

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. (see enclosed spreadsheet)

Would anyone be able to define the required loop?

Many thanks in advance!,

Paul

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

Please Login or Register  to view this content.

**watersev** · 03-12-2011, 06:35 PM

hi, Paul, please check attachment.

Notes:
1. Function returns result when loan_amount = settlement_amount calculated (at least that's how I understand your example)
2. Attachment PaulItFunc1.xls - 7 steps to get result, PaulItFunc1.xls - calculated settlement amount rounded to two digits, 4 steps to get result as in your example

Hope this helps

**tom.hogan** · 03-12-2011, 06:42 PM

Paul,

In your post you said, "LOAN_AMOUNT + FPA_COST equals the SETTLEMENT_AMOUNT" but looking at your spreadsheet the criteria seems to be iterate until LOAN_AMOUNT equals FPA_COST + SETTLEMENT_AMOUNT. If that is in fact the criteria then the below works and generates the same number as E26 in your example.

Regards,

Tom

Please Login or Register  to view this content.

**Paul Schmitz** · 03-13-2011, 01:40 AM

Thank you guys, both solutions work!!

**pike** · 03-13-2011, 02:36 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save