//public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate)
//{
// DataTable dtSchedule = new DataTable();
// try
// {
// dtSchedule = new DataTable();
// dtSchedule.Columns.Add("ID");
// dtSchedule.Columns.Add("StartDate");
// dtSchedule.Columns.Add("EndDate");
// dtSchedule.Columns.Add("CoupFrac");
// dtSchedule.Columns.Add("Notation");
// dtSchedule.Columns.Add("Amortisation");
// dtSchedule.Columns.Add("Factor");
// dtSchedule.Columns.Add("Spread");
// dtSchedule.Columns.Add("CouponPaymentDate");
// dtSchedule.Columns.Add("RiskFreeDP1");
// dtSchedule.Columns.Add("RiskFreeDP2");
// dtSchedule.Columns.Add("FloatingRate");
// dtSchedule.Columns.Add("AllInRate");
// dtSchedule.Columns.Add("Interest");
// dtSchedule.Columns.Add("Days");
// dtSchedule.Columns.Add("AmortisationInt");
// System.Text.StringBuilder sb = new System.Text.StringBuilder();
// DateTime previousStartDate = startDate;
// Decimal firstNationalRow = Convert.ToDecimal(notional);
// Decimal nationalRow = Convert.ToDecimal(notional);
// bool firstAmortizingRow = false;
// int activeCoupons = 0;
// // change this condition to while currCouponDate <= maturityDate
// for (int i = 0; i < rowsCount; i++)
// {
// DataRow dr = dtSchedule.NewRow();
// dr["ID"] = (i + 1).ToString();
// for (int j = 0; j < colsCount; j++)
// {
// if (i == 0 && j == 0)
// {
// //tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// }
// else if (i > 0 && j == 0)
// {
// switch (frequency.Trim())
// {
// case "Monthly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(1).Date;
// //tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Quarterly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(3).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Semi-Annual":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(6).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Annual":
// previousStartDate = startDate;
// startDate = startDate.AddYears(1).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// if (i == 0 && j == 1)
// {
// DateTime endDate;
// switch (frequency.Trim())
// {
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// else if (i > 0 && j == 1)
// {
// DateTime endDate;
// // Compute next coupon date using CoupNCD and correct for weekends
// // f define frequency
// // so now end dates should be generated for the total number of coupons
// CalculatedDates cal = new CalculatedDates();
// System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
// prevCouponDate = currCouponDate;
// currentCouponDate = System.Numeric.Financial.CoupNCD(currentCouponDate, maturityDate, f, d);
// //DateTime previousBusinessDay = PreviousBusinessDay(currentCouponDate);
// //cal.CalculatedDate = previousBusinessDay;
// dr["EndDate"] = currentCouponDate.ToShortDateString();
// //switch (frequency.Trim())
// //{
// // case "Monthly":
// // endDate = startDate.AddMonths(1).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Quarterly":
// // endDate = startDate.AddMonths(3).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Semi-Annual":
// // endDate = startDate.AddMonths(6).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Annual":
// // endDate = startDate.AddYears(1).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // default:
// // break;
// //}
// }
// if (j == 2)
// {
// if (maturityDate != null)
// {
// DateTime maturityDt = Convert.ToDateTime(maturityDate);
// DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
// decimal days = (endDt - maturityDt).Days;
// decimal totalDays = 365;
// decimal coupFreqDays = days / totalDays;
// if (days > 0)
// dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
// else
// dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
// }
// }
// if (j == 3)
// {
// if (i == 0)
// {
// firstNationalRow = Convert.ToDecimal(notional);
// nationalRow = firstNationalRow;
// }
// else
// {
// nationalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
// }
// dr["Notation"] = nationalRow.ToString("N");
// }
// if (j == 4)
// {
// DateTime dt = Convert.ToDateTime(dr["StartDate"]);
// if (couponDate != null && dt >= couponDate)
// {
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
// }
// else
// dr["Amortisation"] = "0.00";
// }
// if (j == 5)
// {
// Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
// dr["Factor"] = factorRow;
// }
// if (j == 6)
// {
// dr["Spread"] = Convert.ToDecimal(spread);
// }
// if (j == 7)
// {
// dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
// }
// if (j == 8)
// {
// if (ccy.ToLower() == "eur")
// {
// EURCurvesBL eurCurveBL = new EURCurvesBL();
// List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in eurCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
// }
// else if (ccy.ToLower() == "usd")
// {
// USDCurveBL usdCurveBL = new USDCurveBL();
// List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in usdCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
// }
// // SplineInterpolator interPolation = new SplineInterpolator();
// // dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
// }
// if (j == 9)
// {
// if (dr["EndDate"].ToString() != string.Empty)
// {
// dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
// }
// }
// if (j == 10)
// {
// if (i == 0)
// {
// dr["FloatingRate"] = 0.5;
// }
// else
// {
// double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
// dr["FloatingRate"] = Convert.ToDouble(d) / 100;
// }
// }
// if (j == 11)
// {
// dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
// }
// if (j == 12)
// {
// dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
// }
// if (j == 13)
// {
// dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
// }
// if (j == 14)
// {
// dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
// }
// }
// dtSchedule.Rows.Add(dr);
// }
// }
// catch (Exception ex)
// {
// }
// return dtSchedule;
//}
public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate, DateTime settlementDate)
{
DataTable dtSchedule = new DataTable();
try
{
dtSchedule = new DataTable();
dtSchedule.Columns.Add("ID");
dtSchedule.Columns.Add("StartDate");
dtSchedule.Columns.Add("EndDate");
dtSchedule.Columns.Add("CoupFrac");
dtSchedule.Columns.Add("Notation");
dtSchedule.Columns.Add("Amortisation");
dtSchedule.Columns.Add("Factor");
dtSchedule.Columns.Add("Spread");
dtSchedule.Columns.Add("CouponPaymentDate");
dtSchedule.Columns.Add("RiskFreeDP1");
dtSchedule.Columns.Add("RiskFreeDP2");
dtSchedule.Columns.Add("FloatingRate");
dtSchedule.Columns.Add("AllInRate");
dtSchedule.Columns.Add("Interest");
dtSchedule.Columns.Add("Days");
dtSchedule.Columns.Add("AmortisationInt");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
DateTime previousStartDate = startDate;
Decimal firstNotionalRow = Convert.ToDecimal(notional);
Decimal notionalRow = Convert.ToDecimal(notional);
bool firstAmortizingRow = false;
int activeCoupons = 0;
// change this condition to while currCouponDate <= maturityDate
int i = 0;
DateTime endDate;
endDate = settlementDate; // this should come from either ( compact, loan details or add new loan tab )
int totalNoOfRows = 0;
while (endDate < maturityDate)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
totalNoOfRows++;
//DateTime newEndDate = PreviousBusinessDay(endDate);
//endDate = newEndDate;
}
endDate = settlementDate;
while (endDate < maturityDate)
{
DataRow dr = dtSchedule.NewRow();
dr["ID"] = (i + 1).ToString();
// for each column in schedule table
for (int j = 0; j < colsCount; j++)
{
if (i == 0 && j == 0)
{
dr["StartDate"] = settlementDate.ToShortDateString();
}
else if (i > 0 && j == 0)
{
// start date always equals the end date of previous coupon
dr["StartDate"] = endDate.ToShortDateString();
}
if (i == 0 && j == 1)
{
//DateTime endDate;
//switch (frequency.Trim())
//{
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
//}
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
else if (i > 0 && j == 1)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
// coup frac calc
if (j == 2)
{
if (settlementDate != null) // removed maturityDate
{
decimal days = (endDate - settlementDate).Days;
decimal totalDays = 365;
decimal coupFreqDays = days / totalDays;
if (days > 0)
dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
else
dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
}
}
if (j == 3)
{
if (i == 0)
{
firstNotionalRow = Convert.ToDecimal(notional);
notionalRow = firstNotionalRow;
}
else
{
notionalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
}
dr["Notation"] = notionalRow.ToString("N");
}
if (j == 4)
{
//DateTime dt = Convert.ToDateTime(dr["StartDate"]);
//if (couponDate != null && dt >= couponDate)
//{
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
//}
//else
// dr["Amortisation"] = "0.00";
dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(firstNotionalRow) / totalNoOfRows).ToString("0.00");
}
if (j == 5)
{
Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
dr["Factor"] = factorRow;
}
if (j == 6)
{
dr["Spread"] = Convert.ToDecimal(spread);
}
if (j == 7)
{
dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
}
if (j == 8)
{
if (ccy.ToLower() == "eur")
{
EURCurvesBL eurCurveBL = new EURCurvesBL();
List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
Dictionary<double, double> rateVals = new Dictionary<double, double>();
foreach (var item in eurCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
else if (ccy.ToLower() == "usd")
{
USDCurveBL usdCurveBL = new USDCurveBL();
List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
Dictionary<double, double> rateVals = new Dictionary<double, double>();
foreach (var item in usdCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
// SplineInterpolator interPolation = new SplineInterpolator();
// dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
}
if (j == 9)
{
if (dr["EndDate"].ToString() != string.Empty)
{
dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
}
}
if (j == 10)
{
if (i == 0)
{
dr["FloatingRate"] = 0.5;
}
else
{
double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
dr["FloatingRate"] = Convert.ToDouble(d) / 100;
}
}
if (j == 11)
{
dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
}
if (j == 12)
{
dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
}
if (j == 13)
{
dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
}
if (j == 14)
{
dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
}
}
if (Convert.ToDecimal(dr[4]) >= 0)
{
dtSchedule.Rows.Add(dr);
i++;
}
}
//if ( dtSchedule.Rows.Count > 0)
//{
// for (int x = 0; x < dtSchedule.Rows.Count; x++)
// {
// dtSchedule.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(notional) / (dtSchedule.Rows.Count)).ToString("0.00");
// if (x == 0)
// {
// firstNotionalRow = Convert.ToDecimal(notional);
// notionalRow = firstNotionalRow;
// }
// else
// {
// notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dtSchedule.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dtSchedule.Rows[x - 1]["Amortisation"])), 2);
// }
// dtSchedule.Rows[x]["Notation"] = notionalRow.ToString("N");
// }
//}
}
catch (Exception ex)
{
}
return dtSchedule;
}
//private void GenerateTable(int colsCount, int rowsCount)
//{
// try
// {
// // timer1.Enabled = false;
// dt1.Clear();
// Session.Remove("LoanScheduleData");
// //Creat the Table and Add it to the Page
// System.Text.StringBuilder sb = new System.Text.StringBuilder();
// //Page.Form.Controls.Add(table);
// //DateTime startDate = txtCouponDate.SelectedDate.Value; commented on 22/02
// DateTime startDate = txtAmortisationsStartDate.SelectedDate.Value;
// DateTime previousStartDate = startDate;
// string frequency = ddlAddCouponFrequency.SelectedValue.ToString();
// Decimal firstNationalRow = Convert.ToDecimal(txtNotional.Text);
// Decimal nationalRow = Convert.ToDecimal(txtNotional.Text);
// // Now iterate through the table and add your controls
// for (int i = 0; i < rowsCount; i++)
// {
// DataRow dr = dt1.NewRow();
// dr["ID"] = (i + 1).ToString();
// //dr["ID"] = (i).ToString();
// for (int j = 0; j < colsCount; j++)
// {
// TableCell cell = new TableCell();
// cell.CssClass = "tdBorder";
// RadTextBox tb = new RadTextBox();
// tb.Skin = "Web20";
// // Set a unique ID for each TextBox added
// tb.ID = "TextBoxRow_" + i + "Col_" + j;
// if (i == 0 && j == 0)
// {
// tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// }
// else if (i > 0 && j == 0)
// {
// switch (frequency.Trim())
// {
// case "Monthly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(1).Date;
// tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Quarterly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(3).Date;
// tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Semi-Annual":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(6).Date;
// tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Annual":
// previousStartDate = startDate;
// startDate = startDate.AddYears(1).Date;
// tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// if (i == 0 && j == 1)
// {
// DateTime endDate;
// switch (frequency.Trim())
// {
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// else if (i > 0 && j == 1)
// {
// DateTime endDate;
// switch (frequency.Trim())
// {
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// if (j == 2)
// {
// DateTime couponDT = AddBusinessDays(DateTime.Now, 10);
// if (txtCouponDate.SelectedDate.ToString() != "")
// {
// couponDT = Convert.ToDateTime(txtCouponDate.SelectedDate.Value);
// }
// DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
// decimal days = (endDt - couponDT).Days;
// decimal totalDays = 365;
// decimal coupFreqDays = days / totalDays;
// if (days > 0)
// dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
// else
// dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
// }
// if (j == 3)
// {
// tb.Text = txtNotional.Text;
// if (i == 0)
// {
// firstNationalRow = Convert.ToDecimal(txtNotional.Text);
// nationalRow = firstNationalRow;
// }
// else
// {
// // Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"])).ToString("N");
// nationalRow = Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"]));
// }
// dr["Notation"] = nationalRow.ToString("N");
// }
// if (j == 4)
// {
// //if (i < 1)
// // dr["Amortisation"] = 0.00;
// //else
// DateTime dt = Convert.ToDateTime(dr["StartDate"]);
// DateTime couponDT = AddBusinessDays(DateTime.Now, 10);
// if (txtCouponDate.SelectedDate.ToString() != "")
// {
// couponDT = Convert.ToDateTime(txtCouponDate.SelectedDate.Value);
// }
// if (txtCouponDate.SelectedDate != null && dt >= couponDT) // change on 09-04
// {
// // int activeCoupons = Convert.ToInt16(txtActiveCoupon.Text); //where to take this value
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
// }
// else
// dr["Amortisation"] = "0.00";
// }
// if (j == 5)
// {
// Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
// tb.Text = factorRow.ToString();
// dr["Factor"] = factorRow;
// }
// if (j == 6)
// {
// dr["Spread"] = Convert.ToDecimal(txtBoxAddMargin.Text);
// }
// if (j == 7)
// {
// dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
// }
// if (j == 8)
// {
// if (ddlAddCurrency.SelectedValue.ToLower() == "eur")
// {
// EURCurvesBL eurCurveBL = new EURCurvesBL();
// List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in eurCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days)) / 365));
// }
// else if (ddlAddCurrency.SelectedValue.ToLower() == "usd")
// {
// USDCurveBL usdCurveBL = new USDCurveBL();
// List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in usdCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days)) / 365));
// }
// // SplineInterpolator interPolation = new SplineInterpolator();
// // dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
// }
// if (j == 9)
// {
// if (dr["EndDate"].ToString() != string.Empty)
// {
// dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (Convert.ToDouble(txtBoxAddMargin.Text) / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(txtBoxAddSigningDate.SelectedDate)).Days) / 360))));
// }
// }
// if (j == 10)
// {
// if (i == 0)
// {
// dr["FloatingRate"] = 0.5;
// }
// else
// {
// double d = Convert.ToDouble(dt1.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dt1.Rows[i - 1]["EndDate"])).Days) / 360;
// dr["FloatingRate"] = Convert.ToDouble(d) / 100;
// }
// }
// if (j == 11)
// {
// dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(txtBoxAddMargin.Text)) / 100;
// }
// if (j == 12)
// {
// dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
// }
// if (j == 13)
// {
// dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
// }
// if (j == 14)
// {
// dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
// }
// // Add the control to the TableCell
// cell.Controls.Add(tb);
// // Add the TableCell to the TableRow
// }
// dt1.Rows.Add(dr);
// // Add the TableRow to the Table
// // table1.Rows.Add(row);
// }
// //table1.Visible = true;
// Session.Add("LoanScheduleData", dt1);
// pnlAmortizing.Visible = true;
// grdAmortizing.Visible = true;
// btnCalculatSchedule.Visible = true;
// grdAmortizing.DataSource = dt1;
// grdAmortizing.DataBind();
// // timer1.Enabled = true;
// }
// catch (Exception)
// {
// }
//}
private void GenerateTable(int colsCount, int rowsCount)
{
try
{
// timer1.Enabled = false;
dt1.Clear();
Session.Remove("LoanScheduleData");
//Creat the Table and Add it to the Page
System.Text.StringBuilder sb = new System.Text.StringBuilder();
//Page.Form.Controls.Add(table);
//DateTime startDate = txtCouponDate.SelectedDate.Value; commented on 22/02
DateTime startDate = txtAmortisationsStartDate.SelectedDate.Value;
DateTime previousStartDate = startDate;
string frequency = ddlAddCouponFrequency.SelectedValue.ToString();
Decimal firstNotionalRow = Convert.ToDecimal(txtNotional.Text);
Decimal notionalRow = Convert.ToDecimal(txtNotional.Text);
// Now iterate through the table and add your controls
//for (int i = 0; i < rowsCount; i++)
//{
// DataRow dr = dt1.NewRow();
// dr["ID"] = (i + 1).ToString();
// //dr["ID"] = (i).ToString();
// dt1.Rows.Add(dr);
// // Add the TableRow to the Table
// // table1.Rows.Add(row);
//}
DateTime settlementDate;
int i = 0;
DateTime endDate;
settlementDate = AddBusinessDays(DateTime.Now, 10); //changed 26-04 asked by avr
endDate = settlementDate;
DateTime maturityDate = txtBoxAddMaturityDate.SelectedDate.Value;
Decimal notional = Convert.ToDecimal(txtNotional.Text);
DateTime couponDate = txtCouponDate.SelectedDate.Value;
string ccy = ddlAddCurrency.SelectedValue.ToString();
DateTime tradedDate = txtBoxAddSigningDate.SelectedDate.Value;
decimal spread = Convert.ToDecimal(txtBoxAddMargin.Text);
int totalNoOfRows = 0;
while (endDate < txtBoxAddMaturityDate.SelectedDate)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
totalNoOfRows++;
//DateTime newEndDate = PreviousBusinessDay(endDate);
//endDate = newEndDate;
}
endDate = AddBusinessDays(txtBoxAddSigningDate.SelectedDate.Value, 10);
while (endDate < txtBoxAddMaturityDate.SelectedDate)
{
DataRow dr = dt1.NewRow();
dr["ID"] = (i + 1).ToString();
// for each column in schedule table
for (int j = 0; j < colsCount; j++)
{
if (i == 0 && j == 0)
{
dr["StartDate"] = settlementDate.ToShortDateString();
}
else if (i > 0 && j == 0)
{
// start date always equals the end date of previous coupon
dr["StartDate"] = endDate.ToShortDateString();
}
if (i == 0 && j == 1)
{
//DateTime endDate;
//switch (frequency.Trim())
//{
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
//}
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
else if (i > 0 && j == 1)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
// coup frac calc
if (j == 2)
{
if (settlementDate != null) // removed maturityDate
{
decimal days = (endDate - settlementDate).Days;
decimal totalDays = 365;
decimal coupFreqDays = days / totalDays;
if (days > 0)
dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
else
dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
}
}
if (j == 3)
{
if (i == 0)
{
firstNotionalRow = Convert.ToDecimal(notional);
notionalRow = firstNotionalRow;
}
else
{
notionalRow = Convert.ToDecimal(Convert.ToDecimal(dt1.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dt1.Rows[i - 1]["Amortisation"]));
}
dr["Notation"] = notionalRow.ToString("N");
}
if (j == 4)
{
//DateTime dt = Convert.ToDateTime(dr["StartDate"]);
//if (couponDate != null && dt >= couponDate)
//{
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
//}
//else
// dr["Amortisation"] = "0.00";
dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(txtNotional.Text) / totalNoOfRows).ToString("0.00");
}
if (j == 5)
{
Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
dr["Factor"] = factorRow;
}
if (j == 6)
{
dr["Spread"] = Convert.ToDecimal(spread);
}
if (j == 7)
{
dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
}
if (j == 8)
{
if (ccy.ToLower() == "eur")
{
EURCurvesBL eurCurveBL = new EURCurvesBL();
List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
Dictionary<double, double> rateVals = new Dictionary<double, double>();
foreach (var item in eurCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
else if (ccy.ToLower() == "usd")
{
USDCurveBL usdCurveBL = new USDCurveBL();
List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
Dictionary<double, double> rateVals = new Dictionary<double, double>();
foreach (var item in usdCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
// SplineInterpolator interPolation = new SplineInterpolator();
// dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
}
if (j == 9)
{
if (dr["EndDate"].ToString() != string.Empty)
{
dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
}
}
if (j == 10)
{
if (i == 0)
{
dr["FloatingRate"] = 0.5;
}
else
{
double d = Convert.ToDouble(dt1.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dt1.Rows[i - 1]["EndDate"])).Days) / 360;
dr["FloatingRate"] = Convert.ToDouble(d) / 100;
}
}
if (j == 11)
{
dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
}
if (j == 12)
{
dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
}
if (j == 13)
{
dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
}
if (j == 14)
{
dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
}
}
if (Convert.ToDecimal(dr[4]) >= 0)
{
dt1.Rows.Add(dr);
i++;
}
}
//for (int x = 0; x < dt1.Rows.Count; x++)
//{
// dt1.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(txtNotional.Text) / (dt1.Rows.Count)).ToString("0.00");
// if (x == 0)
// {
// firstNotionalRow = Convert.ToDecimal(notional);
// notionalRow = firstNotionalRow;
// }
// else
// {
// notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dt1.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dt1.Rows[x - 1]["Amortisation"])), 2);
// }
// dt1.Rows[x]["Notation"] = notionalRow.ToString("N");
//}
//table1.Visible = true;
Session.Add("LoanScheduleData", dt1);
pnlAmortizing.Visible = true;
grdAmortizing.Visible = true;
btnCalculatSchedule.Visible = true;
grdAmortizing.DataSource = dt1;
grdAmortizing.DataBind();
// timer1.Enabled = true;
}
catch (Exception)
{
}
}
//public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate)
//{
// DataTable dtSchedule = new DataTable();
// try
// {
// dtSchedule = new DataTable();
// dtSchedule.Columns.Add("ID");
// dtSchedule.Columns.Add("StartDate");
// dtSchedule.Columns.Add("EndDate");
// dtSchedule.Columns.Add("CoupFrac");
// dtSchedule.Columns.Add("Notation");
// dtSchedule.Columns.Add("Amortisation");
// dtSchedule.Columns.Add("Factor");
// dtSchedule.Columns.Add("Spread");
// dtSchedule.Columns.Add("CouponPaymentDate");
// dtSchedule.Columns.Add("RiskFreeDP1");
// dtSchedule.Columns.Add("RiskFreeDP2");
// dtSchedule.Columns.Add("FloatingRate");
// dtSchedule.Columns.Add("AllInRate");
// dtSchedule.Columns.Add("Interest");
// dtSchedule.Columns.Add("Days");
// dtSchedule.Columns.Add("AmortisationInt");
// System.Text.StringBuilder sb = new System.Text.StringBuilder();
// DateTime previousStartDate = startDate;
// Decimal firstNationalRow = Convert.ToDecimal(notional);
// Decimal nationalRow = Convert.ToDecimal(notional);
// bool firstAmortizingRow = false;
// int activeCoupons = 0;
// // change this condition to while currCouponDate <= maturityDate
// for (int i = 0; i < rowsCount; i++)
// {
// DataRow dr = dtSchedule.NewRow();
// dr["ID"] = (i + 1).ToString();
// for (int j = 0; j < colsCount; j++)
// {
// if (i == 0 && j == 0)
// {
// //tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// }
// else if (i > 0 && j == 0)
// {
// switch (frequency.Trim())
// {
// case "Monthly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(1).Date;
// //tb.Text = startDate.ToShortDateString();
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Quarterly":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(3).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Semi-Annual":
// previousStartDate = startDate;
// startDate = startDate.AddMonths(6).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// case "Annual":
// previousStartDate = startDate;
// startDate = startDate.AddYears(1).Date;
// dr["StartDate"] = startDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// if (i == 0 && j == 1)
// {
// DateTime endDate;
// switch (frequency.Trim())
// {
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
// }
// }
// else if (i > 0 && j == 1)
// {
// DateTime endDate;
// // Compute next coupon date using CoupNCD and correct for weekends
// // f define frequency
// // so now end dates should be generated for the total number of coupons
// CalculatedDates cal = new CalculatedDates();
// System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
// prevCouponDate = currCouponDate;
// currentCouponDate = System.Numeric.Financial.CoupNCD(currentCouponDate, maturityDate, f, d);
// //DateTime previousBusinessDay = PreviousBusinessDay(currentCouponDate);
// //cal.CalculatedDate = previousBusinessDay;
// dr["EndDate"] = currentCouponDate.ToShortDateString();
// //switch (frequency.Trim())
// //{
// // case "Monthly":
// // endDate = startDate.AddMonths(1).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Quarterly":
// // endDate = startDate.AddMonths(3).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Semi-Annual":
// // endDate = startDate.AddMonths(6).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // case "Annual":
// // endDate = startDate.AddYears(1).Date;
// // dr["EndDate"] = endDate.ToShortDateString();
// // break;
// // default:
// // break;
// //}
// }
// if (j == 2)
// {
// if (maturityDate != null)
// {
// DateTime maturityDt = Convert.ToDateTime(maturityDate);
// DateTime endDt = Convert.ToDateTime(dr["EndDate"]);
// decimal days = (endDt - maturityDt).Days;
// decimal totalDays = 365;
// decimal coupFreqDays = days / totalDays;
// if (days > 0)
// dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
// else
// dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
// }
// }
// if (j == 3)
// {
// if (i == 0)
// {
// firstNationalRow = Convert.ToDecimal(notional);
// nationalRow = firstNationalRow;
// }
// else
// {
// nationalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
// }
// dr["Notation"] = nationalRow.ToString("N");
// }
// if (j == 4)
// {
// DateTime dt = Convert.ToDateTime(dr["StartDate"]);
// if (couponDate != null && dt >= couponDate)
// {
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNationalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
// }
// else
// dr["Amortisation"] = "0.00";
// }
// if (j == 5)
// {
// Decimal factorRow = Convert.ToDecimal(nationalRow / firstNationalRow);
// dr["Factor"] = factorRow;
// }
// if (j == 6)
// {
// dr["Spread"] = Convert.ToDecimal(spread);
// }
// if (j == 7)
// {
// dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
// }
// if (j == 8)
// {
// if (ccy.ToLower() == "eur")
// {
// EURCurvesBL eurCurveBL = new EURCurvesBL();
// List<EURCurve> eurCurve = eurCurveBL.GetEURCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in eurCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
// }
// else if (ccy.ToLower() == "usd")
// {
// USDCurveBL usdCurveBL = new USDCurveBL();
// List<USDCurve> usdCurve = usdCurveBL.GetUSCurve();
// Dictionary<double, double> rateVals = new Dictionary<double, double>();
// foreach (var item in usdCurve)
// {
// rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
// }
// var scaler = new SplineInterpolator(rateVals);
// var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
// dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
// }
// // SplineInterpolator interPolation = new SplineInterpolator();
// // dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
// }
// if (j == 9)
// {
// if (dr["EndDate"].ToString() != string.Empty)
// {
// dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
// }
// }
// if (j == 10)
// {
// if (i == 0)
// {
// dr["FloatingRate"] = 0.5;
// }
// else
// {
// double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
// dr["FloatingRate"] = Convert.ToDouble(d) / 100;
// }
// }
// if (j == 11)
// {
// dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
// }
// if (j == 12)
// {
// dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
// }
// if (j == 13)
// {
// dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
// }
// if (j == 14)
// {
// dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
// }
// }
// dtSchedule.Rows.Add(dr);
// }
// }
// catch (Exception ex)
// {
// }
// return dtSchedule;
//}
public DataTable GenerateTable(int colsCount, int rowsCount, DateTime startDate, string frequency, string notional, DateTime maturityDate, DateTime couponDate, Decimal spread, string ccy, DateTime tradedDate, DateTime settlementDate)
{
DataTable dtSchedule = new DataTable();
try
{
dtSchedule = new DataTable();
dtSchedule.Columns.Add("ID");
dtSchedule.Columns.Add("StartDate");
dtSchedule.Columns.Add("EndDate");
dtSchedule.Columns.Add("CoupFrac");
dtSchedule.Columns.Add("Notation");
dtSchedule.Columns.Add("Amortisation");
dtSchedule.Columns.Add("Factor");
dtSchedule.Columns.Add("Spread");
dtSchedule.Columns.Add("CouponPaymentDate");
dtSchedule.Columns.Add("RiskFreeDP1");
dtSchedule.Columns.Add("RiskFreeDP2");
dtSchedule.Columns.Add("FloatingRate");
dtSchedule.Columns.Add("AllInRate");
dtSchedule.Columns.Add("Interest");
dtSchedule.Columns.Add("Days");
dtSchedule.Columns.Add("AmortisationInt");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
DateTime previousStartDate = startDate;
Decimal firstNotionalRow = Convert.ToDecimal(notional);
Decimal notionalRow = Convert.ToDecimal(notional);
bool firstAmortizingRow = false;
int activeCoupons = 0;
// change this condition to while currCouponDate <= maturityDate
int i = 0;
DateTime endDate;
endDate = settlementDate; // this should come from either ( compact, loan details or add new loan tab )
int totalNoOfRows = 0;
while (endDate < maturityDate)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
totalNoOfRows++;
//DateTime newEndDate = PreviousBusinessDay(endDate);
//endDate = newEndDate;
}
endDate = settlementDate;
while (endDate < maturityDate)
{
DataRow dr = dtSchedule.NewRow();
dr["ID"] = (i + 1).ToString();
// for each column in schedule table
for (int j = 0; j < colsCount; j++)
{
if (i == 0 && j == 0)
{
dr["StartDate"] = settlementDate.ToShortDateString();
}
else if (i > 0 && j == 0)
{
// start date always equals the end date of previous coupon
dr["StartDate"] = endDate.ToShortDateString();
}
if (i == 0 && j == 1)
{
//DateTime endDate;
//switch (frequency.Trim())
//{
// case "Monthly":
// endDate = startDate.AddMonths(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Quarterly":
// endDate = startDate.AddMonths(3).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Semi-Annual":
// endDate = startDate.AddMonths(6).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// case "Annual":
// endDate = startDate.AddYears(1).Date;
// tb.Text = endDate.ToShortDateString();
// dr["EndDate"] = endDate.ToShortDateString();
// break;
// default:
// break;
//}
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
else if (i > 0 && j == 1)
{
System.Numeric.DayCountBasis d = System.Numeric.DayCountBasis.Actual365;
System.Numeric.Frequency f = new System.Numeric.Frequency();
if (frequency == "Annual")
{
f = System.Numeric.Frequency.Annual;
}
if (frequency == "Semi-Annual")
{
f = System.Numeric.Frequency.SemiAnnual;
}
if (frequency == "Quarterly")
{
f = System.Numeric.Frequency.Quarterly;
}
endDate = System.Numeric.Financial.CoupNCD(endDate, maturityDate, f, d); // end date is the last coupon settlement date
DateTime newEndDate = PreviousBusinessDay(endDate);
dr["EndDate"] = newEndDate.ToShortDateString();
}
// coup frac calc
if (j == 2)
{
if (settlementDate != null) // removed maturityDate
{
decimal days = (endDate - settlementDate).Days;
decimal totalDays = 365;
decimal coupFreqDays = days / totalDays;
if (days > 0)
{
dr["CoupFrac"] = Convert.ToDecimal(coupFreqDays).ToString("0.00");
}
else
{
dr["CoupFrac"] = Convert.ToDecimal(0).ToString("0.00");
}
}
}
if (j == 3)
{
if (i == 0)
{
firstNotionalRow = Convert.ToDecimal(notional);
notionalRow = firstNotionalRow;
}
else
{
notionalRow = Convert.ToDecimal(Convert.ToDecimal(dtSchedule.Rows[i - 1]["Notation"]) - Convert.ToDecimal(dtSchedule.Rows[i - 1]["Amortisation"]));
}
dr["Notation"] = notionalRow.ToString("N");
}
if (j == 4)
{
//DateTime dt = Convert.ToDateTime(dr["StartDate"]);
//if (couponDate != null && dt >= couponDate)
//{
// if (firstAmortizingRow == false)
// activeCoupons = rowsCount - i;
// Decimal amortisation = Convert.ToDecimal(firstNotionalRow / activeCoupons);
// dr["Amortisation"] = Convert.ToDecimal(amortisation).ToString("0.00");
// firstAmortizingRow = true;
//}
//else
// dr["Amortisation"] = "0.00";
dr["Amortisation"] = Convert.ToDecimal(Convert.ToDecimal(firstNotionalRow) / totalNoOfRows).ToString("0.00");
}
if (j == 5)
{
Decimal factorRow = Convert.ToDecimal(notionalRow / firstNotionalRow);
dr["Factor"] = factorRow;
}
if (j == 6)
{
dr["Spread"] = Convert.ToDecimal(spread);
}
if (j == 7)
{
dr["CouponPaymentDate"] = Convert.ToDateTime(dr["EndDate"]);
}
if (j == 8)
{
if (ccy.ToLower() == "eur")
{
EURCurvesBL eurCurveBL = new EURCurvesBL();
List <EURCurve> eurCurve = eurCurveBL.GetEURCurve();
Dictionary <double, double> rateVals = new Dictionary <double, double>();
foreach (var item in eurCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
else if (ccy.ToLower() == "usd")
{
USDCurveBL usdCurveBL = new USDCurveBL();
List <USDCurve> usdCurve = usdCurveBL.GetUSCurve();
Dictionary <double, double> rateVals = new Dictionary <double, double>();
foreach (var item in usdCurve)
{
rateVals.Add(Convert.ToDouble(item.RateDate.Value.Ticks), Convert.ToDouble(item.Rate));
}
var scaler = new SplineInterpolator(rateVals);
var y = scaler.GetValue(Convert.ToDateTime(dr["EndDate"]).Ticks);
dr["RiskFreeDP1"] = Math.Exp(-((y / 100) * (((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days)) / 365));
}
// SplineInterpolator interPolation = new SplineInterpolator();
// dr["RiskFreeDP1"]= ;//EXP(-(interpolate(schedule.endDate(i),EurDB.Rates,'EurDB.Rates')/100*(schedule.endDate(i)-loan.settlementDate)/365));
}
if (j == 9)
{
if (dr["EndDate"].ToString() != string.Empty)
{
dr["RiskFreeDP2"] = Convert.ToDouble(dr["RiskFreeDP1"]) / Convert.ToDouble((Math.Pow(Convert.ToDouble((1 + (spread / 20000))), ((2 * (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(tradedDate)).Days) / 360))));
}
}
if (j == 10)
{
if (i == 0)
{
dr["FloatingRate"] = 0.5;
}
else
{
double d = Convert.ToDouble(dtSchedule.Rows[i - 1]["RiskFreeDP1"]) / Convert.ToDouble(dr["RiskFreeDP1"]) - 1 / ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dtSchedule.Rows[i - 1]["EndDate"])).Days) / 360;
dr["FloatingRate"] = Convert.ToDouble(d) / 100;
}
}
if (j == 11)
{
dr["AllInRate"] = Convert.ToDouble(Convert.ToDouble(dr["FloatingRate"]) + Convert.ToDouble(spread)) / 100;
}
if (j == 12)
{
dr["Interest"] = ((Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days / 360) * Convert.ToDouble(dr["AllInRate"]) * Convert.ToDouble(dr["Notation"]);
}
if (j == 13)
{
dr["Days"] = (Convert.ToDateTime(dr["EndDate"]) - Convert.ToDateTime(dr["StartDate"])).Days;
}
if (j == 14)
{
dr["AmortisationInt"] = Convert.ToDouble(dr["Amortisation"]) + Convert.ToDouble(dr["Interest"]);
}
}
if (Convert.ToDecimal(dr[4]) >= 0)
{
dtSchedule.Rows.Add(dr);
i++;
}
}
//if ( dtSchedule.Rows.Count > 0)
//{
// for (int x = 0; x < dtSchedule.Rows.Count; x++)
// {
// dtSchedule.Rows[x][5] = Convert.ToDecimal(Convert.ToDecimal(notional) / (dtSchedule.Rows.Count)).ToString("0.00");
// if (x == 0)
// {
// firstNotionalRow = Convert.ToDecimal(notional);
// notionalRow = firstNotionalRow;
// }
// else
// {
// notionalRow = Math.Round(Convert.ToDecimal(Math.Round(Convert.ToDecimal(dtSchedule.Rows[x - 1]["Notation"]), 2) - Convert.ToDecimal(dtSchedule.Rows[x - 1]["Amortisation"])), 2);
// }
// dtSchedule.Rows[x]["Notation"] = notionalRow.ToString("N");
// }
//}
}
catch (Exception ex)
{
}
return(dtSchedule);
}