1 PACKAGE QRM_MM_FORMULAS AUTHID CURRENT_USER AS
2 /* $Header: qrmmmfls.pls 115.28 2003/11/22 00:36:23 prafiuly ship $ */
3
4 e_exceed_vol_upper_bound EXCEPTION;
5 e_option_vol_zero EXCEPTION;
6
7 --bug 3236479
8 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
10
11 /*--------------------------------------------------------------------------
12 BLACK_OPTION_SENS Calculates sensitivities of the interest rate option price using Blacks Formula.(Hull's 4th Edition p.540)
13
14 black_opt_sens_in_rec_typ:
15 IN:
16 p_PRINCIPAL num
17 p_STRIKE_RATE num
18 p_RATE_TYPE_STRIKE varchar2 DEFAULT 'S'
19 p_COMPOUND_FREQ_STRIKE num
20 p_DAY_COUNT_BASIS_STRIKE varchar2
21 p_IR_SHORT num
22 p_RATE_TYPE_SHORT varchar2 DEFAULT 'S'
23 p_COMPOUND_FREQ_SHORT num
24 p_DAY_COUNT_BASIS_SHORT varchar2
25 p_IR_LONG num
26 p_RATE_TYPE_LONG varchar2 DEFAULT 'S'
27 p_COMPOUND_FREQ_LONG num
28 p_DAY_COUNT_BASIS_LONG varchar2
29 p_SPOT_DATE date
30 p_START_DATE date
31 p_MATURITY_DATE date
32 p_VOLATILITY num
33
34 black_opt_sens_out_rec_typ:
35 OUT: P_DELTA_CALL num
36 P_DELTA_PUT num
37 P_THETA_CALL num
38 P_THETA_PUT num
39 P_RHO_CALL num
40 P_RHO_PUT num
41 P_GAMMA num
42 P_VEGA num
43
44 Assumption: Annual Basis = 360
45 Continuous interest rate is required
46
47 Calls XTR_RATE_CONVERSION.rate_conversion to convert day counts and/or between compounded and simple interest rates.
48
49 Calls XTR_MM_FORMULAS.black_option_price to get cumulative normal distribution
50 figures.
51
52 Note: All rates (not spot) are assumed to be in percentage form. Eg: interest rate of 0.08 should be inputted as 8.
53
54 p_PRINCIPAL = the principal amount from which the interest rate is calculated
55 p_STRIKE_RATE = Rx = simple interest rate for the deal
56 p_RATE_TYPE_STRIKE = rate type for p_STRIKE_RATE. 'S' for Simple Rate.
57 'C' for Continuous Rate, and 'P' for Compounding Rate.
58 Default value = 'S' (Simple IR).
59 p_DAY_COUNT_BASIS_STRIKE = day count basis for p_STRIKE_RATE
60 p_IR_SHORT = market simple interest rate for the period between the spot date
61 and the start date
62 p_RATE_TYPE_SHORT = the p_IR_SHORT rate's type. 'S' for Simple Rate.
63 'C' for Continuous Rate, and 'P' for Compounding Rate.
64 Default value = 'S' (Simple IR).
65 p_DAY_COUNT_BASIS_SHORT = day count basis for p_IR_SHORT
66 p_IR_LONG = market simple interest rate for the period between the spot date and
67 the maturity date
68 p_RATE_TYPE_LONG = the p_IR_LONG rate's type. 'S' for Simple Rate. 'C' for
69 Continuous Rate, and 'P' for Compounding Rate. Default value = 'S' (Simple IR)
70 p_DAY_COUNT_BASIS_LONG = day count basis for p_IR_LONG
71 p_SPOT_DATE = the date when the evaluation/calculation is done
72 p_START_DATE = the date when the deal becomes effective.
73 p_END_DATE = the date when the deal matures.
74 p_VOLATILITY = volatility of interest rate per annum
75
76 P_DELTA_CALL = delta call
77 P_DELTA_PUT = delta put
78 P_THETA_CALL = theta call
79 P_THETA_PUT = theta put
80 P_RHO_CALL = rho call
81 P_RHO_PUT = rho put
82 P_GAMMA = gamma
83 P_VEGA = vega
84
85 --------------------------------------------------------------------------*/
86
87 -- added fhu 6/19/01
88 TYPE black_opt_sens_in_rec_type is RECORD(
89 p_PRINCIPAL NUMBER,
90 p_STRIKE_RATE NUMBER,
91 p_RATE_TYPE_STRIKE varchar2(1) DEFAULT 'S',
92 p_COMPOUND_FREQ_STRIKE NUMBER,
93 p_DAY_COUNT_BASIS_STRIKE varchar2(15),
94 p_IR_SHORT NUMBER,
95 p_RATE_TYPE_SHORT varchar2(1) DEFAULT 'S',
96 p_COMPOUND_FREQ_SHORT NUMBER,
97 p_DAY_COUNT_BASIS_SHORT varchar2(15),
98 p_IR_LONG NUMBER,
99 p_RATE_TYPE_LONG varchar2(1) DEFAULT 'S',
100 p_COMPOUND_FREQ_LONG NUMBER,
101 p_DAY_COUNT_BASIS_LONG varchar2(15),
102 p_SPOT_DATE date,
103 p_START_DATE date,
104 p_MATURITY_DATE date,
105 p_VOLATILITY NUMBER);
106
107 TYPE black_opt_sens_out_rec_type is RECORD (p_delta_cap NUMBER,
108 p_delta_floor NUMBER,
109 p_theta_cap NUMBER,
110 p_theta_floor NUMBER,
111 p_rho_cap NUMBER,
112 p_rho_floor NUMBER,
113 p_gamma NUMBER,
114 p_vega NUMBER);
115
116
117 -- added by fhu 6/19/01
118 PROCEDURE black_option_sens(p_in_rec IN black_opt_sens_in_rec_type,
119 p_out_rec IN OUT NOCOPY black_opt_sens_out_rec_type);
120
121
122
123 --#########################################################################
124 --# #
125 --# FUNCTIONS #
126 --# #
127 --#########################################################################
128 --
129 -- added fhu 6/13/01
130 /*
131
132 Calculates DURATION of the following instruments:
133 - bond
134 - discounted securities
135 - forward rate agreement
136 - wholesale term money
137 - interest rate swap
138
139 as specified in: Robert Steiner, Mastering Financial Calculations, p.108
140
141 The arguments are defined as follows:
142 - p_pvc_array = contains present values stored in xtr_num_table;
143 value at index k corresponds to present value of kth
144 cashflow; = null for discounted securities and FRA;
145 k = 1,2,3,...
146 - p_days_array = number of days until kth cashflow; each kth element
147 corresponds to kth present value; for discounted
148 securities and FRA, array contains only one element
149 representing days to maturity; k=1,2,3,...
150 - p_days_in_year = number of days in a year
151 */
152
153 FUNCTION duration(p_pvc_array IN XTR_MD_NUM_TABLE,
154 p_days_array IN XTR_MD_NUM_TABLE,
155 p_days_in_year NUMBER)
156 RETURN NUMBER;
157
158
159 -- added fhu 6/13/01
160 /*
161 Calculates MODIFIED DURATION of the following instruments:
162 - bond
163 - discounted securities
164 - interest rate swap
165
166 as specified in: Robert Steiner, Mastering Financial Calculations, p.110
167
168 The arguments are defined as follows:
169 - p_duration = duration of instrument
170 - p_yield = if bond, is yield per annum based on p_num_payments per
171 year (YTM); if discounted security, is yield rate;
172 if interest rate swap, is internal rate of return (IRR)
173 - p_num_payments = number of payments per year; if discounted security,
174 value = 1
175 */
176
177 FUNCTION mod_duration(p_duration NUMBER,
178 p_yield NUMBER,
179 p_num_payments NUMBER)
180 RETURN NUMBER;
181
182
183 -- added fhu 6/13/01
184 /*
185 Calculates BOND CONVEXITY as specified in: Robert Steiner, Mastering Financial Calculations, p.112
186
187 The arguments are defined as follows:
188 - p_cf_array = cashflows stored in xtr_md_num_table; value at index k
189 corresponds to kth cashflow; k=1,2,3,...
190 - p_days_array = number of days until kth cashflow, stored in
191 xtr_md_num_table; each kth element correspons to kth
192 cashflow; k=1,2,3,...
193 - p_num_payments = number of payments per year
194 - p_yield = yield per annum based on p_num_payments per year (YTM)
195 - p_days_in_year = number of days in year
196 - p_dirty_price = dirty price of bond
197 */
198
199 FUNCTION bond_convexity(p_cf_array XTR_MD_NUM_TABLE,
200 p_days_array XTR_MD_NUM_TABLE,
201 p_num_payments NUMBER,
202 p_yield NUMBER,
203 p_days_in_year NUMBER,
204 p_dirty_price NUMBER)
205 RETURN NUMBER;
206
207
208 -- added fhu 6/13/01
209 /*
210 Calculates DELTA/DOLLAR DURATION,given modified duration, of the following
211 instruments:
212 - bond
213 - discounted security
214 as specified in: Robert Steiner, Mastering Financial Calculations, p.110
215
216 The arguments are defined as follows:
217 - p_OUT = 'DELTA' if output is to be delta, 'DOLLAR' if value is to be
218 dollar duration
219 - p_dirty_price = dirty price of bond
220 - p_mod_duration = modified duration
221
222 Note: a delta yield of 1% (0.01) is assumed for sensitivities calculations
223 */
224
225
226 FUNCTION delta_md(p_out VARCHAR2,
227 p_dirty_price NUMBER,
228 p_mod_duration NUMBER)
229 RETURN NUMBER;
230
231
232
233 -- added fhu 6/13/01
234 /*
235 Calculates DELTA/DOLLAR DURATION of a bond, given bond convexity, as specified
236 in: Robert Steiner, Mastering Financial Calculations, p.112
237
238 The arguments are defined as follows:
239 - p_out = 'DELTA' if p_VALUE is to be delta, 'DOLLAR' if is to be
240 dollar duration
241 - p_dirty_price = dirty price of bond
242 - p_mod_duration = modified duration of bond
243 - p_convexity = convexity of bond
244
245 Note: a delta yield of 1% (0.01) is assumed for sensitivities calculations
246 */
247
248 FUNCTION bond_delta_convexity(p_out VARCHAR2,
249 p_dirty_price NUMBER,
250 p_mod_duration NUMBER,
251 p_convexity NUMBER)
252 RETURN NUMBER;
253
254
255
256 -- added fhu 6/13/01
257 /*
258 Calculates BPV(DR), or change in price due to a 1 basis point change in
259 discount rate, of discounted security. See Deal Calculations HLD.
260
261 The arguments are defined as follows:
262 - p_principle = principle amount or face value
263 - p_days_to_mat = days to maturity
264 - p_days_in_year = days in year
265 */
266
267 FUNCTION ni_bpv_dr(p_principle NUMBER,
268 p_days_to_mat NUMBER,
269 p_days_in_year NUMBER)
270 RETURN NUMBER;
271
272
273 -- added fhu 6/13/01
274 /*
275 Calculates BPV(YR), or change in price due to a 1 basis point change in yield
276 rate, of discounted security or bond. See Deal Calculations HLD.
277
278 The arguments are defined as follows:
279 - p_dirty_price = dirty price of discounted security or bond
280 - p_mod_duration = modified duration of discounted security or bond
281 */
282
283 FUNCTION bpv_yr(p_dirty_price NUMBER,
284 p_mod_duration NUMBER)
285 RETURN NUMBER;
286
287
288
289 -- added fhu 6/13/01
290 /*
291 Calculates DELTA/DOLLAR DURATION of discounted security, given BPV(YR) or
292 BPV(DR). See Deal Calculations HLD.
293
294 The arguments are defined as follows:
295 - p_out = 'DELTA' if output is to be delta, 'DOLLAR' if is to be
296 dollar duration
297 - p_bpv = BPV(DR) or BPV(YR)
298 */
299
300 FUNCTION ni_delta_bpv(p_out VARCHAR2,
301 p_bpv NUMBER)
302 RETURN NUMBER;
303
304
305
306 -- added fhu 6/13/01
307 /*
308 Calcuation of BPV for interest rate swaps (IRS) and wholesale term money (TMM).
309 This call also be used to calculate the BPV of any instrument, given its fair
310 values. See: Deal Calculations HLD, Deal Valuations HLD, FRA Calculator HLD.
311
312 Arguments defined as follows:
313 - p_fair_value_base = fair value with regular yield curve/rate
314 - p_fair_value_shifted = fair value with yield curve/rate shifted up
315 one basis point
316 */
317
318 FUNCTION bpv(p_fair_value_base NUMBER,
319 p_fair_value_shifted NUMBER)
320 RETURN NUMBER;
321
322
323 --added by sankim 8/8/01
324 /*
325 Calculates NI CONVEXITY and FRA COVEXITY as specified in: Robert Steiner,
326 Mastering Financial
327 Calculations, p.112
328
329 The arguments are defined as follows:
330 - p_num_days = number of days until cash flow occurs
331 i.e. number of days till maturity for NI or
332 number of days till settlement for FRA
333 - p_rate = yield rate of discounted security, or settlement rate of fra
334 - p_days_in_year = number of days in year
335
336 Note: rate is assumed to be in percentage form.
337 */
338
339 FUNCTION ni_fra_convexity( p_num_days IN NUMBER,
340 p_rate IN NUMBER,
341 p_days_in_year IN NUMBER)
342 RETURN NUMBER;
343
344
345 --added by jbrodsky 9/21/01
346 /*
347 Calculates implied volatility for options, specifically FXO, IRO, and BS
348
349 The arguments are defined as follows:
350 - p_inidactor = type of option ('FXO', 'IRO', or 'BS')
351 - p_spot_date = spot date where the option value is evaluated
352 - p_expiration_date = the date of the option expiring
353 - p_interest_rates = table contains different interest rates needed as follows by index
354
355 INDEX VALUE
356 FXO 1 Domestic
357 FXO 2 Foreign
358 IRO 1 Strike
359 IRO 2 Short
360 IRO 3 Long
361
362 - p_day_count_basis = day count basis table for different rates. Corresponds to index listed above.
363 - p_rate_type = rate type table for different rates. 'S' for simple, 'C' for continuous, 'P'
364 for compounding. Corresponds to index listed above.
365 - p_compound_freq = table of frequencies of discretely compounded rate. Only necessary if rate type
366 is 'P'. Corresponds to index listed above.
367 - p_spot_rate = current underlying rate or price.
368 - p_strike_rate = strike rate or price agreed upon for option.
369 - p_option_price = price of option in currency.
370 - p_option_type = type of the option 'C' = call, 'P' = put
371 - p_start_date = date when deal becomes effective (only for IRO)
372 - p_principal num = principal amount for the interest rate (only for IRO)
373 - p_error_tol = error tolerance. Defaulted to .00001 .
374 - p_max_iteration = max iterations in bisection calculation. Defaulted to 100.
375 - p_max_value = maximum value limitation. Defaulted to 1,000,000,000.
376 - p_vol_first_guess = first guess of implied volatility from which the real implied vol is calculated
377 Defaulted to 30 (percent)
378
379 */
380
381 FUNCTION calculate_implied_volatility(p_indicator IN VARCHAR2,
382 p_spot_date IN DATE,
383 p_expiration_date IN DATE,
384 p_interest_rates IN XTR_MD_NUM_TABLE,
385 p_day_count_basis IN SYSTEM.QRM_VARCHAR_TABLE,
386 p_rate_type IN SYSTEM.QRM_VARCHAR_TABLE,
387 p_compound_freq IN XTR_MD_NUM_TABLE,
388 p_spot_rate IN NUMBER,
392 p_start_date IN DATE,
389 p_strike_rate IN NUMBER,
390 p_option_price IN NUMBER,
391 p_option_type IN VARCHAR2,
393 p_principal IN NUMBER,
394 p_error_tol IN NUMBER DEFAULT 0.00001,
395 p_max_iterations IN NUMBER DEFAULT 100,
396 p_max_value IN NUMBER DEFAULT 1000000000,
397 p_vol_first_guess IN NUMBER DEFAULT 30)
398 RETURN NUMBER;
399
400
401 /* Fair Value Calculations -- fhu 12/13/01 */
402
403
404 FUNCTION calculate_fwd_rate( p_set_code VARCHAR2,
405 p_bpv VARCHAR2, -- 'Y' or 'N'
406 p_deal_subtype VARCHAR2,
407 p_day_count_basis VARCHAR2,
408 p_ccy VARCHAR2,
409 p_interpolation_method VARCHAR2,
410 p_spot_date DATE,
411 p_start_date DATE,
412 p_maturity_date DATE)
413 RETURN NUMBER;
414
415
416 FUNCTION within_one_year(p_start_date DATE, p_end_date DATE)
417 RETURN BOOLEAN;
418
419 -- day count basis is for contract rate
420 -- forward rate (theoretical fra price) is expressed in ACTUAL/365
421 PROCEDURE fv_fra(p_price_model IN VARCHAR2,
422 p_set_code IN VARCHAR2,
423 p_bpv IN VARCHAR2, -- 'Y' or 'N'
424 p_deal_subtype IN VARCHAR2,
425 p_ccy IN VARCHAR2,
426 p_interpolation_method IN VARCHAR2,
427 p_spot_date IN DATE,
428 p_start_date IN DATE,
429 p_maturity_date IN DATE,
430 p_face_value IN NUMBER,
431 p_contract_rate IN NUMBER,
432 p_day_count_basis IN VARCHAR2,-- for contract rate
433 p_side IN OUT NOCOPY VARCHAR2,
434 p_fwd_fwd_rate IN OUT NOCOPY NUMBER,
435 p_fair_value IN OUT NOCOPY NUMBER);
436
437
438 PROCEDURE fv_iro(p_price_model IN VARCHAR2,
439 p_set_code IN VARCHAR2,
440 p_deal_subtype IN VARCHAR2,
441 p_ccy IN VARCHAR2,
442 p_interpolation_method IN VARCHAR2,
443 p_spot_date IN DATE,
444 p_start_date IN DATE,
445 p_maturity_date IN DATE,
446 p_strike IN NUMBER,
447 p_day_count_basis_strike IN VARCHAR2, -- for strike rate
448 p_amount IN NUMBER,
449 p_side IN OUT NOCOPY VARCHAR2,
450 p_fwd_fwd_rate IN OUT NOCOPY NUMBER,
451 p_fair_value IN OUT NOCOPY NUMBER);
452
453
454 PROCEDURE fv_ni(p_price_model IN VARCHAR2,
455 p_set_code IN VARCHAR2,
456 p_deal_subtype IN VARCHAR2,
457 p_discount_basis IN VARCHAR2, -- 'Y' or 'N'
458 p_ccy IN VARCHAR2,
459 p_interpolation_method IN VARCHAR2,
460 p_day_count_basis IN VARCHAR2, -- for reval rate
461 p_spot_date IN DATE,
462 p_start_date IN DATE,
463 p_maturity_date IN DATE,
464 p_face_value IN NUMBER,
465 p_margin IN NUMBER,
466 p_side IN OUT NOCOPY VARCHAR2,
467 p_reval_rate IN OUT NOCOPY NUMBER,
468 p_fair_value IN OUT NOCOPY NUMBER);
469
470
471 PROCEDURE fv_bond( p_price_model IN VARCHAR2,
472 p_set_code IN VARCHAR2,
473 p_deal_subtype IN VARCHAR2,
474 /* xtr_bond_issues.ric_code */
475 p_bond_code IN VARCHAR2,
476 p_bond_issue_code IN VARCHAR2,
477 p_ccy IN VARCHAR2,
478 p_interpolation_method IN VARCHAR2,
479 p_coupon_action IN VARCHAR2,
480 /* for coupon rate */
481 p_day_count_basis IN VARCHAR2,
482 p_spot_date IN DATE,
483 /* WDK: don't need this value!
484 p_start_date IN DATE,
485 */
486 p_maturity_date IN DATE,
487 p_coupon_rate IN NUMBER,
488 p_face_value IN NUMBER,
489 p_margin IN NUMBER,
490 p_rounding_type IN VARCHAR2,
491 p_day_count_type IN VARCHAR2,
492 p_side IN OUT NOCOPY VARCHAR2,
493 p_clean_price_reval IN OUT NOCOPY NUMBER,
494 p_dirty_price IN OUT NOCOPY NUMBER,
495 p_ytm IN OUT NOCOPY NUMBER,
496 p_accrued_interest IN OUT NOCOPY NUMBER,
497 p_fair_value IN OUT NOCOPY NUMBER,
498 p_actual_ytm OUT NOCOPY NUMBER);
499
500
501
502 PROCEDURE fv_tmm_irs_rtmm(
503 p_price_model IN VARCHAR2,
504 p_deal_type IN VARCHAR2,
505 p_set_code IN VARCHAR2,
506 p_bpv IN VARCHAR2,--'Y' or 'N'
507 p_deal_subtype IN VARCHAR2,
508 p_interpolation_method IN VARCHAR2,
509 p_ccy IN VARCHAR2,
510 p_discount_basis IN VARCHAR2, -- for IRS
511 p_initial_basis IN VARCHAR2, -- for IRS
512 p_spot_date IN DATE,
513 p_settle_date IN DATE, -- for TMM/RTMM
514 p_margin IN NUMBER,
515 p_last_rec_trans_no IN NUMBER,
516 p_day_count_basis IN VARCHAR2,-- int rates
517 p_transaction_nos IN XTR_MD_NUM_TABLE,--inc order
518 p_start_dates IN SYSTEM.QRM_DATE_TABLE,
519 p_maturity_dates IN SYSTEM.QRM_DATE_TABLE,
520 p_settle_dates IN SYSTEM.QRM_DATE_TABLE,
521 p_coupon_due_on_dates IN SYSTEM.QRM_DATE_TABLE, -- prepaid interest
522 p_interest_refunds IN XTR_MD_NUM_TABLE, -- prepaid interest
523 p_principal_actions IN SYSTEM.QRM_VARCHAR_TABLE,
524 p_interest_rates IN XTR_MD_NUM_TABLE,
525 -- interest settled for IRS/TMM
526 -- amount due for RTMM
527 p_interest_settled IN XTR_MD_NUM_TABLE,
528 -- nvl(p_principal_adjusts, 0) before calling
529 p_principal_adjusts IN XTR_MD_NUM_TABLE,
530 p_accum_interests IN XTR_MD_NUM_TABLE,
531 p_accum_interests_bf IN XTR_MD_NUM_TABLE,-- bug 2807340
535 p_pv_cashflows IN OUT NOCOPY XTR_MD_NUM_TABLE,
532 p_balance_outs IN XTR_MD_NUM_TABLE,
533 p_settle_term_interests IN SYSTEM.QRM_VARCHAR_TABLE,--TMM
534 p_side IN OUT NOCOPY VARCHAR2,
536 p_cf_days IN OUT NOCOPY XTR_MD_NUM_TABLE,
537 p_annual_basis IN OUT NOCOPY NUMBER,
538 p_trans_rate IN OUT NOCOPY NUMBER,
539 p_accrued_int IN OUT NOCOPY NUMBER,
540 p_fair_value IN OUT NOCOPY NUMBER);
541
542
543
544 FUNCTION qrm_calc_interest(
545 p_principle IN NUMBER,
546 p_start_date IN DATE,
547 p_end_date IN DATE,
548 p_rate IN NUMBER,
549 p_day_count_basis IN VARCHAR2)
550 RETURN NUMBER;
551
552
553 FUNCTION calculate_accrued_interest(p_indicator VARCHAR2,
554 p_ref_date DATE,
555 p_start_date DATE,
556 p_maturity_date DATE,
557 p_interest_rate NUMBER,
558 p_interest NUMBER,
559 p_accum_interest_bf NUMBER,
560 p_balance_out NUMBER,
561 p_no_of_days NUMBER,
562 p_day_count_basis VARCHAR2,
563 p_accum_int_action VARCHAR2)
564 RETURN NUMBER;
565
566 END;
[Home] [Help]