void AddDefinedNamesForAnnuity()
{
string sheetName = GetSheetNameWrappedInQuotes();
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Clear();
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_payment", sheetName + "!$I$4");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Interest_Rate_Per_Month", "=Interest_Rate/Number_of_Payments_Per_Year");
definedNames.Add("Actual_Number_Payments", "=NPER(Interest_Rate_Per_Month, " + sheetName + "!$I$4+Extra_Payments, -Loan_Amount)");
}
void AddDefinedNamesForAnnuityPayments()
{
string sheetName = "'" + Sheet.Name + "'";
char separator = workbook.Options.Culture.TextInfo.ListSeparator[0];
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_payment", sheetName + "!$I$4");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Interest_Rate_Per_Month", "=Interest_Rate/Number_of_Payments_Per_Year");
definedNames.Add("Actual_Number_Payments", "=NPER(Interest_Rate_Per_Month" + separator + " " + sheetName + "!$I$4+Extra_Payments" + separator + " -Loan_Amount)");
}
void AddDefinedNamesForScaled()
{
string sheetName = GetSheetNameWrappedInQuotes();
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Clear();
definedNames.Add("Values_Entered", "IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
definedNames.Add("Full_Print", sheetName + "!$A:$K");
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_Monthly_Payment", "Loan_Amount/(Loan_Years*Number_of_Payments_Per_Year)");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Real_Number_Payments", sheetName + "!$I$6");
definedNames.Add("Total_Early_Payments", sheetName + "!$I$7");
definedNames.Add("Total_Interest", sheetName + "!$I$8");
definedNames.Add("Beg_Bal", sheetName + "!$D$12:$D$" + ScheduledLastRow);
definedNames.Add("Cum_Int", sheetName + "!$K$12:$K$" + ScheduledLastRow);
definedNames.Add("Data", sheetName + "!$B$12:$K$" + ScheduledLastRow);
definedNames.Add("End_Bal", sheetName + "!$J$12:$J$" + ScheduledLastRow);
definedNames.Add("Extra_Pay", sheetName + "!$F$12:$F$" + ScheduledLastRow);
definedNames.Add("Header_Row", "ROW(" + sheetName + "!$17:$17)");
definedNames.Add("Int", sheetName + "!$I$12:$I$" + ScheduledLastRow);
definedNames.Add("Last_Row", "IF(Values_Entered,Header_Row+Number_of_Payments,Header_Row)");
definedNames.Add("Number_of_Payments", "=MATCH(0.01,End_Bal,-1)+1");
definedNames.Add("Pay_Date", sheetName + "!$C$12:$C$" + ScheduledLastRow);
definedNames.Add("Pay_Num", sheetName + "!$B$12:$B$" + ScheduledLastRow);
definedNames.Add("Payment_Date", "DATE(YEAR(Loan_Start),MONTH(Loan_Start)+Payment_Number,DAY(Loan_Start))");
definedNames.Add("Princ", sheetName + "!$H$12:$H$" + ScheduledLastRow);
definedNames.Add("Print_Area_Reset", "OFFSET(Full_Print,0,0,Last_Row)");
definedNames.Add("Sched_Pay", sheetName + "!$E$12:$E$" + ScheduledLastRow);
definedNames.Add("Total_Pay", sheetName + "!$G$12:$G$" + ScheduledLastRow);
definedNames.Add("Total_Payment", "Scheduled_Payment+Extra_Payment");
definedNames.Add("Payment_Number", "ROW()-Header_Row");
definedNames.Add("Loan_Not_Paid", "IF(Payment_Number<=Number_of_Payments,1,0)");
}
