import pandas as pd
import matplotlib.pyplot as plt

# Load the dataset
df_sora = pd.read_csv("/Path/to/your/sora_data.csv")  # Replace with the actual file path

# Convert date column to datetime format
df_sora = df_sora.rename(columns={"SORA Publication Date": "date", 
                                  "Compound SORA - 1 month": "SORA_1M", 
                                  "Compound SORA - 3 month": "SORA_3M"})

# Convert SORA values to numeric
df_sora["SORA_1M"] = pd.to_numeric(df_sora["SORA_1M"], errors='coerce')
df_sora["SORA_3M"] = pd.to_numeric(df_sora["SORA_3M"], errors='coerce')

df_sora = df_sora.rename(columns={"SORA Publication Date": "date", 
                                  "Compound SORA - 1 month": "SORA_1M", 
                                  "Compound SORA - 3 month": "SORA_3M"})

df_sora["date"] = pd.to_datetime(df_sora["date"], errors='coerce')

# Convert SORA values to numeric
df_sora["SORA_1M"] = pd.to_numeric(df_sora["SORA_1M"], errors='coerce')
df_sora["SORA_3M"] = pd.to_numeric(df_sora["SORA_3M"], errors='coerce')

# Drop NaN values in date column
df_sora = df_sora.dropna(subset=["date"])

# Sorting data by date
df_sora = df_sora.sort_values(by="date")

# Plot 1-month and 3-month SORA over time
plt.figure(figsize=(12, 6))
plt.plot(df_sora["date"], df_sora["SORA_1M"], label="1-Month SORA", color="blue")
plt.plot(df_sora["date"], df_sora["SORA_3M"], label="3-Month SORA", color="red", linestyle="dashed")

plt.xlabel("Year")
plt.ylabel("SORA Rate (%)")
plt.title("1-Month and 3-Month SORA Rates (2005-2025)")
plt.legend()
plt.grid(True)
plt.show()

# Summary statistics of Compound SORA rates
sora_1m_mean = df_sora["SORA_1M"].mean()
sora_3m_mean = df_sora["SORA_3M"].mean()

# Recent values (last available data points)
sora_1m_recent = df_sora["SORA_1M"].dropna().iloc[-1]
sora_3m_recent = df_sora["SORA_3M"].dropna().iloc[-1]

# Estimate mortgage rates by adding a typical bank spread (0.8% to 1.5%)
bank_spread_range = (0.8, 1.5)

mortgage_estimate_1m = (sora_1m_mean + bank_spread_range[0], sora_1m_mean + bank_spread_range[1])
mortgage_estimate_3m = (sora_3m_mean + bank_spread_range[0], sora_3m_mean + bank_spread_range[1])

# Prepare results
sora_estimates = {
    "1-Month Compound SORA": {"Historical Avg": sora_1m_mean, "Recent Value": sora_1m_recent},
    "3-Month Compound SORA": {"Historical Avg": sora_3m_mean, "Recent Value": sora_3m_recent},
    "Estimated Mortgage Rate (1M SORA)": mortgage_estimate_1m,
    "Estimated Mortgage Rate (3M SORA)": mortgage_estimate_3m
}

sora_estimates

import pandas as pd
import matplotlib.pyplot as plt

# Load the CSV file
df = pd.read_csv("/Path/to/your/data.csv")  # Replace with the actual file path

# Extracting year and quarter number from the 'quarter' column
df["year"] = df["quarter"].str[:4].astype(int)
df["quarter_number"] = df["quarter"].str[-1:].astype(int)

# Creating a proper datetime column for plotting
df["date"] = pd.to_datetime(df["year"].astype(str) + "Q" + df["quarter_number"].astype(str))

# Filter for Non-Landed properties
non_landed_df = df[df["property_type"] == "Non-Landed"].sort_values("date")

# Plot the index over time
plt.figure(figsize=(12, 6))
plt.plot(non_landed_df["date"], non_landed_df["index"], linestyle="-")
plt.xlabel("Year")
plt.ylabel("Property Price Index")
plt.title("Non-Landed Property Price Index Over Time")
plt.grid(True)

# Show the plot
plt.show()

# Calculate the percentage change in the index (returns)
non_landed_df["returns"] = non_landed_df["index"].pct_change()

# Calculate the standard deviation of the returns as a measure of volatility
volatility = non_landed_df["returns"].std()

# Display the calculated volatility
print('volatility:', volatility)

# Get the initial and final values of the index
initial_value = non_landed_df["index"].iloc[0]
final_value = non_landed_df["index"].iloc[-1]

# Calculate the annual growth rate
non_landed_df["year"] = non_landed_df["date"].dt.year
annual_growth = non_landed_df.groupby("year")["index"].last().pct_change() * 100  # Convert to percentage

# Plot the annual growth rate over time
plt.figure(figsize=(12, 6))
plt.plot(annual_growth.index, annual_growth, linestyle="-", color="blue")
plt.xlabel("Year")
plt.ylabel("Annual Growth Rate (%)")
plt.title("Annual Growth Rate of Non-Landed Property Price Index Over Time")
plt.axhline(y=0, color="black", linestyle="--")  # Reference line at 0%
plt.grid(True)

# Show the plot
plt.show()

# Calculate the number of years
num_years = (non_landed_df["date"].iloc[-1] - non_landed_df["date"].iloc[0]).days / 365.25

# Compute CAGR
cagr = (final_value / initial_value) ** (1 / num_years) - 1

# Display the CAGR as a percentage
cagr_percentage = cagr * 100
print('CAGR start from 1975:', cagr_percentage)

# Filter data for the last 20 years
recent_20_years_df = non_landed_df[non_landed_df["year"] >= (non_landed_df["year"].max() - 19)]

# Get the initial and final values of the index for the last 20 years
initial_value_20 = recent_20_years_df["index"].iloc[0]
final_value_20 = recent_20_years_df["index"].iloc[-1]

# Calculate the number of years (20 years)
num_years_20 = 20

# Compute CAGR for the last 20 years
cagr_20 = (final_value_20 / initial_value_20) ** (1 / num_years_20) - 1

# Display the CAGR as a percentage
cagr_20_percentage = cagr_20 * 100
print('CAGR over last 20 years:', cagr_20_percentage)

import pandas as pd
import matplotlib.pyplot as plt

# Load the datasets
price_index_df = pd.read_csv("/Path/to/your/price_index_data.csv")  # Replace with the actual file path
rental_index_df = pd.read_csv("/Path/to/your/rental_index_data.csv")  # Replace with the actual file path

# Filter data for Non-Landed properties
price_index_nl = price_index_df[price_index_df["property_type"] == "Non-Landed"][["quarter", "index"]]
rental_index_nl = rental_index_df[(rental_index_df["property_type"] == "Non-Landed") & 
                                  (rental_index_df["locality"] == "Whole Island")][["quarter", "index"]]

# Merge datasets on quarter
merged_df = pd.merge(price_index_nl, rental_index_nl, on="quarter", suffixes=("_price", "_rental"))

# Convert quarter to datetime format for better plotting
merged_df["quarter"] = pd.to_datetime(merged_df["quarter"].str.replace("Q", "-") + "-01")

# Calculate rental over price ratio
merged_df["rental_over_price"] = merged_df["index_rental"] / merged_df["index_price"] * 4.8


# Plot rental over price ratio
plt.figure(figsize=(12, 6))
plt.plot(merged_df["quarter"], merged_df["rental_over_price"], label="Rental over Price Ratio", color='red')

plt.xlabel("Year")
plt.ylabel("Ratio")
plt.title("Rental over Price Ratio Over Time (%)")
plt.legend()
plt.grid(True)
plt.show()

# Calculate the average rental yield
average_rental_yield = merged_df["rental_over_price"].mean()
average_rental_yield

# Helper function to calculate the rental property tax
def calculate_property_tax(annual_value):
    if annual_value <= 30000:
        tax = annual_value * 0.12
    elif annual_value <= 45000:
        tax = (30000 * 0.12) + ((annual_value - 30000) * 0.20)
    elif annual_value <= 60000:
        tax = (30000 * 0.12) + (15000 * 0.20) + ((annual_value - 45000) * 0.28)
    else:
        tax = (30000 * 0.12) + (15000 * 0.20) + (15000 * 0.28) + ((annual_value - 60000) * 0.36)
    
    return tax


# Helper function to calculate the monthly ops cost
def ops_cost(property_price):
    monthly_rental_income = property_price * rental_yield / 12
    # Assumptions based on research
    annual_value = property_price * 0.03  # Estimated AV at 3% of property value
    
    # Property Tax Calculation
    property_tax = calculate_property_tax(annual_value)
    
    # Condo Management Fees (for condominiums, estimated $200 to $500 per month)
    maintenance_cost_per_month = 350  # Taking an average
    annual_management_cost = maintenance_cost_per_month * 12
    
    # Repairs & Renovations (estimate 0.5% of property price annually)
    repair_costs = property_price * 0.005
    
    # Insurance (Estimated up to $150 annually)
    insurance_cost = 150  # Taking an average
    
    # Agent Commission (Half-month rent for a 1-year lease)
    agent_commission = monthly_rental_income * 0.5
        
    # Total Annual Operating Cost Calculation
    total_operating_cost = (
        property_tax
        + annual_management_cost
        + repair_costs
        + insurance_cost
        + agent_commission
    )

    return (property_price, int(monthly_rental_income), int(total_operating_cost / 12))

result = ops_cost(1_200_000)
print('monthly operational cost:', result[2])
print('monthly rental income:', result[1])

# Helper function to calculate the maximum loan amount
def calculate_loan_amount(emi, r, n):
    return emi * ((1 - (1 + r) ** -n) / r)


# Helper function to calculate the down payment if expect to rental to cover monthly mortgage and ops cost
def calculate_down_payment(property_value, monthly_rental_income, ops_cost):
    legal_fee = 3000
    valuation_fee = 1000
    
    emi = monthly_rental_income - ops_cost
    
    max_loan_amount = calculate_loan_amount(emi, interest_rate / 12, loan_tenure_years * 12)

    bsd_amount = calculate_bsd(property_price)
    
    # Required down payment
    required_down_payment = property_value - max_loan_amount
    total_upfront  = required_down_payment + bsd_amount + legal_fee + valuation_fee
    
    return (int(max_loan_amount), int(total_upfront))


result = calculate_down_payment(1_200_200, 3_800, 1_420)
print('loan amount:', result[0])
print('total upfront investment amount:', result[1])

"""
Data to support the calculation
"""
rental_yield = 0.038 # reference to estimated rental_yield average for last 20 years at 3.8%
interest_rate = 0.025 # reference to the average interest rate calculated from SORA historical data at 2.5%
CAGR = 0.046 # reference to last 20 years CAGR of API 4.69% 

"""
Functions to support the calculation
"""

# Helper function to calcualte the bsd
def calculate_bsd(property_price):
    """
    Function to calculate Buyer's Stamp Duty (BSD) in Singapore based on property price.
    """
    # BSD Rates and Brackets
    brackets = [180000, 180000, 640000, 500000]  # BSD brackets
    rates = [0.01, 0.02, 0.03, 0.04, 0.05]  # Corresponding BSD rates

    bsd = 0
    remaining_price = property_price

    for i in range(len(brackets)):
        if remaining_price > brackets[i]:
            bsd += brackets[i] * rates[i]
            remaining_price -= brackets[i]
        else:
            bsd += remaining_price * rates[i]
            remaining_price = 0
            break

    # Any remaining amount is taxed at 5%
    if remaining_price > 0:
        bsd += remaining_price * rates[-1]

    return bsd


# Helper function to calculate the rental property tax
def calculate_property_tax(annual_value):
    if annual_value <= 30000:
        tax = annual_value * 0.12
    elif annual_value <= 45000:
        tax = (30000 * 0.12) + ((annual_value - 30000) * 0.20)
    elif annual_value <= 60000:
        tax = (30000 * 0.12) + (15000 * 0.20) + ((annual_value - 45000) * 0.28)
    else:
        tax = (30000 * 0.12) + (15000 * 0.20) + (15000 * 0.28) + ((annual_value - 60000) * 0.36)
    
    return tax


# Helper function to calculate the monthly ops cost
def ops_cost(property_price):
    monthly_rental_income = property_price * rental_yield / 12
    # Assumptions based on research
    annual_value = property_price * 0.03  # Estimated AV at 3% of property value
    
    # Property Tax Calculation
    property_tax = calculate_property_tax(annual_value)
    
    # Condo Management Fees (for condominiums, estimated $200 to $500 per month)
    maintenance_cost_per_month = 350  # Taking an average
    annual_management_cost = maintenance_cost_per_month * 12
    
    # Repairs & Renovations (estimate 0.5% of property price annually)
    repair_costs = property_price * 0.005
    
    # Insurance (Estimated up to $150 annually)
    insurance_cost = 150  # Taking an average
    
    # Agent Commission (Half-month rent for a 1-year lease)
    agent_commission = monthly_rental_income * 0.5
        
    # Total Annual Operating Cost Calculation
    total_operating_cost = (
        property_tax
        + annual_management_cost
        + repair_costs
        + insurance_cost
        + agent_commission
    )

    return (property_price, int(monthly_rental_income), int(total_operating_cost / 12))
    

# Helper function to calculate the maximum loan amount
def calculate_loan_amount(emi, r, n):
    return emi * ((1 - (1 + r) ** -n) / r)


# Helper function to calculate the down payment if expect to rental to cover monthly mortgage and ops cost
def calculate_down_payment(property_value, monthly_rental_income, ops_cost):
    legal_fee = 3000
    valuation_fee = 1000
    
    emi = monthly_rental_income - ops_cost
    
    max_loan_amount = calculate_loan_amount(emi, interest_rate / 12, loan_tenure_years * 12)

    bsd_amount = calculate_bsd(property_price)
    
    # Required down payment
    required_down_payment = property_value - max_loan_amount
    total_upfront  = required_down_payment + bsd_amount + legal_fee + valuation_fee
    
    return (int(max_loan_amount), int(total_upfront))


# Helper function to calculate the future value of the property price
def future_value(P, r, year):
    FV = P * (1 + r) ** year
    return int(FV)


"""
Calculate for investment property price range from 1,200,000 to 1,600,00 with loan tenure of 20 years
"""

loan_tenure_years = 20
property_prices = [1_200_000, 1_300_000, 1_400_000, 1_500_000, 1_600_000]

rentals = [ops_cost(p) for p in property_prices]

loan_amounts_down_payments = [calculate_down_payment(r[0], r[1], r[2]) for r in rentals]

print('property value\t', 'down payment\t', 'monthly loan amount\t', 'monthly rental income\t', 'price appreciation\t')
for idx, rental in enumerate(rentals):
    print(rentals[idx][0], '\t', 
          loan_amounts_down_payments[idx][1], '\t', 
          rentals[idx][1] - rentals[idx][2], '\t\t\t', 
          rentals[idx][1], '\t\t\t',
          future_value(rentals[idx][0], CAGR, loan_tenure_years))