ROI Calculator with Scenarios

Sub ROICalculator()
    Dim ws As Worksheet
    Dim initialInvestment As Double
    Dim annualCashFlow As Double
    Dim years As Integer
    Dim discountRate As Double
    Dim scenarios As Variant
    Dim i As Long

    ' Create new worksheet
    Set ws = Worksheets.Add
    ws.Name = "ROI_Analysis_" & Format(Date, "mmyyyy")

    ' Get input parameters
    initialInvestment = CDbl(InputBox("Enter initial investment amount:"))
    annualCashFlow = CDbl(InputBox("Enter expected annual cash flow:"))
    years = CInt(InputBox("Enter investment period (years):"))
    discountRate = CDbl(InputBox("Enter discount rate (e.g., 0.10 for 10%):"))

    ' Set up headers
    ws.Range("A1").Value = "ROI Analysis Report"
    ws.Range("A1").Font.Size = 16
    ws.Range("A1").Font.Bold = True

    ws.Range("A3").Value = "Investment Parameters:"
    ws.Range("A4").Value = "Initial Investment:"
    ws.Range("B4").Value = initialInvestment
    ws.Range("B4").NumberFormat = "£#,##0.00"

    ws.Range("A5").Value = "Annual Cash Flow:"
    ws.Range("B5").Value = annualCashFlow
    ws.Range("B5").NumberFormat = "£#,##0.00"

    ws.Range("A6").Value = "Investment Period:"
    ws.Range("B6").Value = years & " years"

    ws.Range("A7").Value = "Discount Rate:"
    ws.Range("B7").Value = discountRate
    ws.Range("B7").NumberFormat = "0.00%"

    ' Calculate metrics
    Dim totalCashFlow As Double
    Dim npv As Double
    Dim roi As Double
    Dim paybackPeriod As Double

    totalCashFlow = annualCashFlow * years
    roi = (totalCashFlow - initialInvestment) / initialInvestment
    paybackPeriod = initialInvestment / annualCashFlow

    ' Calculate NPV
    npv = -initialInvestment
    For i = 1 To years
        npv = npv + (annualCashFlow / ((1 + discountRate) ^ i))
    Next i

    ' Display results
    ws.Range("A9").Value = "Results:"
    ws.Range("A10").Value = "Simple ROI:"
    ws.Range("B10").Value = roi
    ws.Range("B10").NumberFormat = "0.00%"

    ws.Range("A11").Value = "Net Present Value:"
    ws.Range("B11").Value = npv
    ws.Range("B11").NumberFormat = "£#,##0.00"

    ws.Range("A12").Value = "Payback Period:"
    ws.Range("B12").Value = paybackPeriod & " years"

    ' Scenario analysis
    scenarios = Array(-0.2, -0.1, 0, 0.1, 0.2)  ' -20% to +20%

    ws.Range("A14").Value = "Scenario Analysis:"
    ws.Range("A15").Value = "Cash Flow Change"
    ws.Range("B15").Value = "New NPV"
    ws.Range("C15").Value = "New ROI"

    For i = 0 To UBound(scenarios)
        Dim scenarioCashFlow As Double
        Dim scenarioNPV As Double
        Dim scenarioROI As Double
        Dim j As Long

        scenarioCashFlow = annualCashFlow * (1 + scenarios(i))
        scenarioNPV = -initialInvestment

        For j = 1 To years
            scenarioNPV = scenarioNPV + (scenarioCashFlow / ((1 + discountRate) ^ j))
        Next j

        scenarioROI = ((scenarioCashFlow * years) - initialInvestment) / initialInvestment

        ws.Cells(16 + i, 1).Value = Format(scenarios(i), "0%")
        ws.Cells(16 + i, 2).Value = scenarioNPV
        ws.Cells(16 + i, 2).NumberFormat = "£#,##0.00"
        ws.Cells(16 + i, 3).Value = scenarioROI
        ws.Cells(16 + i, 3).NumberFormat = "0.00%"
    Next i

    ' Format and autofit
    ws.Columns.AutoFit
    ws.Range("A3:A12,A14:A15").Font.Bold = True

    MsgBox "ROI analysis completed with scenario analysis!"
End Sub