Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Do I need to use VBA for the following control structure?
Hi all,
I have the following control structure from Matlab, do you think I need VBA to handle it, or I can just rely on Excel? Thanks a lot! ----------------------------------------- for t=1:5; CumLoss=CumLoss+sum(DefaultTime(:, t)); if (CumLossTotalNotional*Deattachment) break; else PV=PV+y*(TotalNotional-CumLoss)/(1+r)^t; if t==5 PV=PV+(TotalNotional-CumLoss)/(1+r)^t; end; end; end; |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Do I need to use VBA for the following control structure?
You can just rely on Excel:
In row 1 put the labels t,DefaultTime, CumLoss,PV,TotalNotional,Deattachment,r,y respectively in columns A - H Leave A2,B2 blank. In C2,D2 put in initial values of CumLoss and PV (perhaps 0) and in cells E2:H2 put in the values of the parameters TotalNotional to y Select E1:H2 and goto Insert/Names/Create and click ok on the default "create in top row" in cells A3:A7 put in the numbers 1 to 5 and in cells B3:B7 put in the values of DefaultTime. In cell C3 put in the formula =C2+SUM($B$3:B3) (which should correspond to CumLoss=CumLoss+sum(DefaultTime(:, t)); - but I don't know matab and am just guessing what (:,t) means - is the implied lower bound 1, or is there a 0 you haven't mentioned. If DefaultTime(0) exists - put it in cell B2 and replace the sum by SUM($B $2:B3)) and copy it down through C7 In cell D3 put the formula =IF(C3TotalNotional*Deattachment,D2,IF(A3<5,D2+y* (TotalNotional-C3)/ (1+r_)^A3,D2+(TotalNotional-C3)/(1+r_)^A3)) (with no line breaks - I hope Google doesn't mangle it). Note that for some reason "r" is a reserved name in Excel (hence the underscore automatically added on creation) but "y" isn't. Strange and copy it through D7. The formula assumes that the numbers in DefaultTime are non-negative (a connotation from the word "cummulative"). If this assumption is false and you don't want PV to change if CumLoss drops back below TotalNotional*Deattachment, the formula would have to be adjusted a bit. VBA of course *could* handle this and might give you more flexibility. The moral of the story: In Excel 1) Names can be given to represent named parameters 2) Iteration is acheived by dragging a formula down a range 3) relatively complicated conditional logic can be acheived with built in logical functions The chief disadvantage is that the number of iterations needs to be fixed (or at least given a fixed upper bound) at design time. Hope that helps -John Coleman On Mar 1, 2:11 am, "cfman" wrote: Hi all, I have the following control structure from Matlab, do you think I need VBA to handle it, or I can just rely on Excel? Thanks a lot! ----------------------------------------- for t=1:5; CumLoss=CumLoss+sum(DefaultTime(:, t)); if (CumLossTotalNotional*Deattachment) break; else PV=PV+y*(TotalNotional-CumLoss)/(1+r)^t; if t==5 PV=PV+(TotalNotional-CumLoss)/(1+r)^t; end; end; end; |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Do I need to use VBA for the following control structure?
It depends if you want the answer as a single response or you want the answer
in multple cells I would make a column with t in the first column and number the rows from 0 to 5. the use excel functions to put the answerr in a second column. "cfman" wrote: Hi all, I have the following control structure from Matlab, do you think I need VBA to handle it, or I can just rely on Excel? Thanks a lot! ----------------------------------------- for t=1:5; CumLoss=CumLoss+sum(DefaultTime(:, t)); if (CumLossTotalNotional*Deattachment) break; else PV=PV+y*(TotalNotional-CumLoss)/(1+r)^t; if t==5 PV=PV+(TotalNotional-CumLoss)/(1+r)^t; end; end; end; |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula structure | Excel Worksheet Functions | |||
Do I need to use VBA for the following control structure? | Excel Discussion (Misc queries) | |||
Wacky Little IF Structure | Excel Discussion (Misc queries) | |||
if structure help | Excel Programming | |||
Structure of If...Else in VBA | Excel Programming |