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;

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;

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;