private void _TestProperty()
{
clsProperty property = new clsProperty(14);
Console.WriteLine(property.PropertyID() + "," + property.Address());
clsCSVTable tbl = new clsCSVTable(clsProperty.strPropertyPath);
tbl.SaveAs("/Users/" + Environment.UserName + "/Documents/Professional/Resilience/tblPropertyTest.csv");
clsProperty badproperty = new clsProperty("no", "no", "no", "no", -1, "no");
Console.WriteLine(badproperty.Save("/Users/" + Environment.UserName + "/Documents/Professional/Resilience/tblPropertyTest.csv"));
tbl = new clsCSVTable(clsProperty.strPropertyPath);
string[] strNewRecord = new string[tbl.Width() - 1];
for (int i = 0; i < strNewRecord.Length; i++)
{
strNewRecord[i] = "new " + i.ToString();
}
tbl.New(strNewRecord);
tbl.SaveAs("/Users/" + Environment.UserName + "/Documents/Professional/Resilience/tblPropertyTest.csv");
}
partial void AddButtonPushed(AppKit.NSButton sender)
{
// store values
string address = this.AddressBox.StringValue;
string town = this.TownBox.StringValue;
string county = this.CountyBox.StringValue;
string state = this.StateBox.StringValue;
int titleHolderID = (int)this.TitleHolderPopUp.IndexOfSelectedItem - 3;
int coID = (int)this.CoBorrowerPopUp.IndexOfSelectedItem - 3;
int lenderID = (int)this.LenderPopUp.IndexOfSelectedItem - 3;
int titleID = (int)this.TitlePopUp.IndexOfSelectedItem - 3;
DateTime acquisitionDate = (DateTime)this.PurchaseDatePicker.DateValue;
double price = this.PurchasePriceBox.DoubleValue;
double bpo = this.BPOBox.DoubleValue;
double rehabCost = this.RehabCostBox.DoubleValue;
double pnl = this.PnLBox.DoubleValue;
double months = this.MonthsToCompletionBox.DoubleValue;
double loanRate = 0.09;
double penaltyRate = 0.05;
// acquisition cost estimates
double processingCost;
double recordingCost;
double acquisitionTaxes;
double titlePolicyCost;
double HOICost;
double propertyTaxes;
double initialDraw = -4500D;
double programFee = -2000D;
double commissions;
double transferTax;
double proRatedPropertyTax;
double otherBudgetedCosts = initialDraw - programFee; // accounting, travel, utilities, inspections, etc.
double concessionPercentage = 0.02;
double commissionPercentage = 0.06;
double totalBackEndCosts;
// other stuff
double dispostion;
double totalCommitment;
double hardInterestGuess;
double pnlGuess;
int streetNumber;
string streetName;
this.UpdateMessage.StringValue = "";
// check State.length == 2
if (state.Length != 2)
{
this.UpdateMessage.StringValue = "Invalid State ID (Length must be 2)";
}
// check date > today
else if (acquisitionDate < System.DateTime.Today)
{
this.UpdateMessage.StringValue = "Acquisition Date can not be in the past.";
}
// check no duplicates in entities
else if ((titleHolderID == coID) || (titleHolderID == lenderID) || (titleHolderID == titleID) ||
(coID == lenderID) || (coID == titleID) || (lenderID == titleID))
{
this.UpdateMessage.StringValue = "Duplicate Entities - please check TitleHolder, CoBorrower, Lender and Title Company";
}
// check address has numbers and letters
else if ((!Int32.TryParse(System.Text.RegularExpressions.Regex.Match(address, @"\d+").Value, out streetNumber)) ||
(streetName = System.Text.RegularExpressions.Regex.Replace(address, streetNumber.ToString(), "").Trim()) == "")
{
this.UpdateMessage.StringValue = "Invalid Street Address, must be {streetnumber} {streetname}";
}
// check all values are positive
else if (price * rehabCost * months * pnl * bpo < 0.001)
{
this.UpdateMessage.StringValue = "Missing Values in price, rehab, PnL, BPO, or Months";
}
// check things like lender elgible, borrower eligible, title company state eligible
// Check valid address??
// Check county??
else
{
// calculate estimated acquisition costs
switch (state)
{
case "NJ":
processingCost = -1150D;
recordingCost = -415D;
acquisitionTaxes = 0D;
titlePolicyCost = -620 - 0.0044 * price;
HOICost = -450 - 0.0054 * price;
propertyTaxes = -0.015 * price;
proRatedPropertyTax = bpo * 0.002 * months; // roughly 2.4% per year in NJ
transferTax = bpo * 0.01;
break;
case "MD":
processingCost = -875D;
recordingCost = -1090D - 0.0025 * price;
acquisitionTaxes = -190D - 0.0066 * price;
titlePolicyCost = -463D - 0.0042 * price;
HOICost = -1017D - 0.0025 * price;
propertyTaxes = -0.009 * price;
proRatedPropertyTax = bpo * 0.0009 * months; // roughly 1.1% per year in MD
transferTax = bpo * 0.01;
break;
case "PA":
processingCost = -250D;
recordingCost = -425D - 0.0025 * price;
acquisitionTaxes = -450D - 0.02 * price;
titlePolicyCost = -1330D - 0.005 * price;
HOICost = -417D - 0.0144 * price;
propertyTaxes = -0.017 * price;
proRatedPropertyTax = bpo * 0.00125 * months; // roughly 1.5% per year in PA
transferTax = bpo * 0.01;
break;
default:
processingCost = -250D;
recordingCost = -425D - 0.0025 * price;
acquisitionTaxes = -450D - 0.02 * price;
titlePolicyCost = -1330D - 0.005 * price;
HOICost = -417D - 0.0144 * price;
propertyTaxes = -0.017 * price;
proRatedPropertyTax = bpo * 0.00125 * months; // roughly 1.5% per year in PA
transferTax = bpo * 0.01;
break;
}
// calculate rough estimated other costs
totalCommitment = price + rehabCost -
(initialDraw + processingCost + recordingCost + acquisitionTaxes + titlePolicyCost + HOICost + propertyTaxes);
commissions = bpo * commissionPercentage;
// total back end costs are Accrued Property Tax, Sale Transfer Tax (typically split with buyer), Sale Commissons,
// seller concession / assistance, and Hard Interest
hardInterestGuess = (totalCommitment - 0.5 * rehabCost) * (months / 12D) * loanRate;
totalBackEndCosts = proRatedPropertyTax + transferTax + commissions + hardInterestGuess + bpo * concessionPercentage;
pnlGuess = bpo - (totalCommitment + totalBackEndCosts);
// Check estimated PnL based on State, Purchase, Rehab - Message Box variance, reject if outside $X
UpdateMessage.StringValue += "\n Estimated vs Quoted PnL: \t";
UpdateMessage.StringValue += pnlGuess.ToString("000,000.00") + " vs " + pnl.ToString("000,000.00");
dispostion = totalCommitment + hardInterestGuess + 0.5 * pnlGuess;
// if valid, then create new property, new loan, new cashflows, new documents
clsProperty newProperty = new clsProperty(address, town, county, state, bpo, streetName);
newProperty.Save();
clsLoan newLoan = new clsLoan(newProperty.ID(), titleHolderID, coID, titleID, lenderID,
acquisitionDate, acquisitionDate.AddMonths(9), loanRate, penaltyRate);
int newLoanID = newLoan.ID();
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
-price, false, clsCashflow.Type.AcquisitionPrice));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
0D, false, clsCashflow.Type.AcquisitionConcession));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
processingCost, false, clsCashflow.Type.AcquisitionProcessing));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
recordingCost, false, clsCashflow.Type.AcquisitionRecording));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
acquisitionTaxes, false, clsCashflow.Type.AcquisitionTaxes));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
HOICost, false, clsCashflow.Type.HomeownersInsurance));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
initialDraw, false, clsCashflow.Type.InitialExpenseDraw));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
titlePolicyCost, false, clsCashflow.Type.TitlePolicy));
// assume entire rehab draw comes halfway through the rehab process, and that there is a two month lag from rehab completion to sale closing
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)(0.5 * (months - 2D))), System.DateTime.Today,
System.DateTime.MaxValue, newLoanID, -rehabCost, false, clsCashflow.Type.RehabDraw));
// disposition cashflow
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)months), System.DateTime.Today, System.DateTime.MaxValue,
newLoanID, dispostion, false, clsCashflow.Type.NetDispositionProj));
newLoan.SetNewOriginationDate(acquisitionDate);
newLoan.Save();
// Create Documents
for (int i = 0; i < Enum.GetValues(typeof(clsDocument.Type)).Length; i++)
{
clsDocument newDoc = new clsDocument(((clsDocument.Type)i).ToString(), newProperty.ID(), (clsDocument.Type)i);
newDoc.Save();
}
// write summary to text label (this.UpdateMessage.StringValue)
this.UpdateMessage.StringValue += String.Format("\n\nCompleted adding new loan ({0}), new cashflows, new property ({1}), and new docs ",
newLoan.ID().ToString(),
newProperty.ID().ToString());
}
}
partial void AddButtonPushed(AppKit.NSButton sender)
{
// store values
string address = this.AddressBox.StringValue;
string town = this.TownBox.StringValue;
string county = this.CountyBox.StringValue;
string state = this.StateBox.StringValue;
int titleHolderID = clsEntity.EntityID(this.TitleHolderPopUp.TitleOfSelectedItem);
int coID = clsEntity.EntityID(this.CoBorrowerPopUp.TitleOfSelectedItem);
int lenderID = clsEntity.EntityID(this.LenderPopUp.TitleOfSelectedItem);
int titleID = clsEntity.EntityID(this.TitlePopUp.TitleOfSelectedItem);
DateTime acquisitionDate = (DateTime)this.PurchaseDatePicker.DateValue;
double price = this.PurchasePriceBox.DoubleValue;
double bpo = this.BPOBox.DoubleValue;
double rehabCost = this.RehabCostBox.DoubleValue;
double pnl = this.PnLBox.DoubleValue;
double months = this.MonthsToCompletionBox.DoubleValue;
double loanRate = this.LoanRateBox.DoubleValue;
double penaltyRate = this.DefaultRateBox.DoubleValue;
double points = this.PointsBox.DoubleValue;
double profitSplit = this.ProfitSplitBox.DoubleValue;
bool acqOnly = (this.AcquisitionOnlyCheckBox.State == NSCellStateValue.On);
bool bfullAcqCostFunded = (this.FullAcquisitionCostCheckBox.State == NSCellStateValue.On);
double initialFundingMult = this.InitialLoanPercentBox.DoubleValue;
// acquisition cost estimates
CostEstimate costEstimateForState;
double initialDraw = -4500D;
double programFee = -2000D;
double otherBudgetedCosts = initialDraw - programFee; // accounting, travel, utilities, inspections, etc.
double concessionPercentage = 0.02;
double commissionPercentage = 0.06;
double totalBackEndCosts;
// other stuff
double dispostion;
double totalCommitment;
double upfrontCommitment;
double hardInterestGuess;
double pnlGuess;
int streetNumber;
string streetName;
this.UpdateMessage.StringValue = "";
// check State.length == 2
if (state.Length != 2)
{
this.UpdateMessage.StringValue = "Invalid State ID (Length must be 2)";
}
// check date > today
else if (acquisitionDate < System.DateTime.Today)
{
this.UpdateMessage.StringValue = "Acquisition Date can not be in the past.";
}
// check no duplicates in entities
else if ((titleHolderID == lenderID) || (titleHolderID == titleID) ||
(coID == lenderID) || (coID == titleID) || (lenderID == titleID))
{
this.UpdateMessage.StringValue = "Duplicate Entities - please check TitleHolder, CoBorrower, Lender and Title Company";
}
// check address has numbers and letters
else if ((!Int32.TryParse(System.Text.RegularExpressions.Regex.Match(address, @"\d+").Value, out streetNumber)) ||
(streetName = System.Text.RegularExpressions.Regex.Replace(address, streetNumber.ToString(), "").Trim()) == "")
{
this.UpdateMessage.StringValue = "Invalid Street Address, must be {streetnumber} {streetname}";
}
// check all values are positive
else if (price * months * bpo < 0.001)
{
this.UpdateMessage.StringValue = "Missing Values in price, BPO, or Months";
}
else if ((rehabCost <= 0) && (!acqOnly))
{
this.UpdateMessage.StringValue = "Rehab cost missing for non-acquisition-only loan";
}
else if ((pnl <= 0) && (profitSplit > 0))
{
this.UpdateMessage.StringValue = "Esimtated PnL Missing or Negative for loan with profit split";
}
// check things like lender elgible, borrower eligible, title company state eligible
// Check valid address??
// Check county??
else
{
// Create new Property, Loan and Documents in tables
if (coID == -1)
{
coID = titleHolderID;
}
clsProperty newProperty = new clsProperty(address, town, county, state, bpo, streetName);
newProperty.Save();
clsLoan newLoan = new clsLoan(newProperty.ID(), titleHolderID, coID, titleID, lenderID, acquisitionDate,
acquisitionDate.AddMonths(9), loanRate, penaltyRate, points, profitSplit, acqOnly);
int newLoanID = newLoan.ID();
for (int i = 0; i < Enum.GetValues(typeof(clsDocument.Type)).Length; i++)
{
clsDocument newDoc = new clsDocument(((clsDocument.Type)i).ToString(), newProperty.ID(), (clsDocument.Type)i);
newDoc.Save();
}
#region Create Cashflows, contingent on Loan Parameters
costEstimateForState = new CostEstimate(state, price, bpo, months);
if (bfullAcqCostFunded)
{
totalCommitment = (price + rehabCost - initialDraw - costEstimateForState.AcquisitionClosingCosts) / (1D - points * 0.01);
upfrontCommitment = (totalCommitment - rehabCost / (1D - points * 0.01));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
-price, false, clsCashflow.Type.AcquisitionPrice));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
0D, false, clsCashflow.Type.AcquisitionConcession));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
costEstimateForState.ProcessingCost, false, clsCashflow.Type.AcquisitionProcessing));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
costEstimateForState.RecordingCost, false, clsCashflow.Type.AcquisitionRecording));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
costEstimateForState.AcquisitionTaxes, false, clsCashflow.Type.AcquisitionTaxes));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
costEstimateForState.HOICost, false, clsCashflow.Type.HomeownersInsurance));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
initialDraw, false, clsCashflow.Type.InitialExpenseDraw));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
costEstimateForState.TitlePolicyCost, false, clsCashflow.Type.TitlePolicy));
if (points > 0)
{
// upfront points
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
0.01 * points * upfrontCommitment, false, clsCashflow.Type.Points));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
-0.01 * points * upfrontCommitment, false, clsCashflow.Type.Points));
}
if (!acqOnly)
{
// assume entire rehab draw comes halfway through the rehab process, and that there is a two month lag from rehab completion to sale closing
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)(0.5 * (months - 2D))), System.DateTime.Today,
System.DateTime.MaxValue, newLoanID, -rehabCost, false, clsCashflow.Type.RehabDraw));
if (points > 0)
{
// points on construction draws
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)(0.5 * (months - 2D))), System.DateTime.Today,
System.DateTime.MaxValue, newLoanID,
0.01 * points * rehabCost / (1D - points * 0.01),
false, clsCashflow.Type.Points));
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)(0.5 * (months - 2D))), System.DateTime.Today,
System.DateTime.MaxValue, newLoanID,
-0.01 * points * rehabCost / (1D - points * 0.01),
false, clsCashflow.Type.Points));
}
}
}
else
{
totalCommitment = price * initialFundingMult;
newLoan.AddCashflow(new clsCashflow(acquisitionDate, DateTime.Today, DateTime.MaxValue, newLoanID,
-price, false, clsCashflow.Type.AcquisitionPrice));
newLoan.AddCashflow(new clsCashflow(acquisitionDate, DateTime.Today, DateTime.MaxValue, newLoanID,
-price * (initialFundingMult - 1D), false, clsCashflow.Type.InitialExpenseDraw));
if (points > 0)
{
newLoan.AddCashflow(new clsCashflow(acquisitionDate, System.DateTime.Today, System.DateTime.MaxValue, newLoanID,
0.01 * points * totalCommitment, false, clsCashflow.Type.Points));
}
}
// disposition cashflow
hardInterestGuess = (totalCommitment - 0.5 * rehabCost) * (months / 12D) * loanRate;
if (profitSplit > 0)
{
totalBackEndCosts = costEstimateForState.SaleTaxes + hardInterestGuess + bpo * (concessionPercentage + commissionPercentage);
pnlGuess = bpo - (totalCommitment + totalBackEndCosts);
if (!bfullAcqCostFunded)
{
pnlGuess += -totalCommitment * points * 0.01;
} // if points were payed upfront, subtract from PnL
UpdateMessage.StringValue += "\n Estimated vs Quoted PnL: \t";
UpdateMessage.StringValue += pnlGuess.ToString("000,000.00") + " vs " + pnl.ToString("000,000.00");
}
else
{
pnlGuess = 0D;
}
dispostion = totalCommitment + hardInterestGuess + profitSplit * pnl; // user-given pnl rather than system estimate
newLoan.AddCashflow(new clsCashflow(acquisitionDate.AddMonths((int)months), System.DateTime.Today, System.DateTime.MaxValue,
newLoanID, dispostion, false, clsCashflow.Type.NetDispositionProj));
#endregion
newLoan.SetNewOriginationDate(acquisitionDate);
newLoan.Save();
// write summary to text label (this.UpdateMessage.StringValue)
this.UpdateMessage.StringValue += String.Format("\n\nCompleted adding new loan ({0}), new cashflows, new property ({1}), and new docs ",
newLoan.ID().ToString(),
newProperty.ID().ToString());
}
}
