SPB Git

Repository Public

GoalSeekTests.swift 13.4 KB 239 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/WhatIfTests/GoalSeekTests.swift10// Module           : WhatIfTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90import AnalyticsTestSupport91@testable import WhatIf9293@Suite struct GoalSeekTests {94    func options(_ isolation: WhatIfIsolation) -> GoalSeekOptions { var o = GoalSeekOptions(); o.isolation = isolation; return o }9596    @Test func loanModelAgainstAnalyticAnswers() throws {97        for isolation in WhatIfIsolation.allCases {98            let m = Model()99            m.seedHistory()100            let r = 0.06 / 12101            // principal affordable with a payment of 1500: P = pmt·(1 − (1+r)^−n)/r102            let p = try expectUntouched(m, isolation, "principal") {103                try GoalSeek.run(engine: m.engine, setCell: m.cell("B4"), toValue: 1500, byChanging: m.cell("B3"), options: options(isolation))104            }105            #expect(p.converged && !p.committed && p.method == "brent")106            #expect(isClose(p.solution ?? .nan, 1500 * (1 - pow(1 + r, -360)) / r, rel: 1e-11), "principal \(p.solution ?? .nan)")107            #expect(abs(p.residual) <= 1500e-9 && isClose(p.achievedValue, 1500, rel: 1e-9) && p.startValue == 300_000)108            #expect(p.bracketLower != nil && p.bracketLower! <= p.solution! && p.solution! <= p.bracketUpper!)109            // term for a payment of 2500: n = −ln(1 − P·r/pmt)/ln(1+r)110            let n = try expectUntouched(m, isolation, "term") {111                try GoalSeek.run(engine: m.engine, setCell: m.cell("B4"), toValue: 2500, byChanging: m.cell("B2"), options: options(isolation))112            }113            #expect(n.converged)114            #expect(isClose(n.solution ?? .nan, -log(1 - 300_000 * r / 2500) / log(1 + r), rel: 1e-9), "term \(n.solution ?? .nan)")115            // rate for a payment of 2000: no closed form; verify by substitution and against bisection116            let rate = try expectUntouched(m, isolation, "rate") {117                try GoalSeek.run(engine: m.engine, setCell: m.cell("B4"), toValue: 2000, byChanging: m.cell("B1"), options: options(isolation))118            }119            #expect(rate.converged)120            var lo = 0.01, hi = 0.20121            for _ in 0..<200 { let mid = (lo + hi) / 2; if annuity(300_000, mid, 360) < 2000 { lo = mid } else { hi = mid } }122            #expect(isClose(rate.solution ?? .nan, lo, rel: 1e-9), "rate \(rate.solution ?? .nan) vs \(lo)")123            #expect(isClose(annuity(300_000, rate.solution ?? .nan, 360), 2000, rel: 1e-9))124            #expect(rate.evaluations > 3 && rate.table().count == 11)125        }126    }127128    @Test func irrThroughADefinedNameOnAnotherSheet() throws {129        for isolation in WhatIfIsolation.allCases {130            let m = Model()131            let r = try expectUntouched(m, isolation, "irr") {132                try GoalSeek.run(engine: m.engine, setCell: m.cell("B5", sheet: 2), toValue: 0, byChanging: m.cell("B1", sheet: 2), options: options(isolation))133            }134            // −1000 + 600/x + 600/x² = 0 → x = (600 + √(600² + 4·1000·600)) / 2000135            let x = (600 + (360_000.0 + 2_400_000).squareRoot()) / 2000136            #expect(r.converged && isClose(r.solution ?? .nan, x - 1, rel: 1e-9), "irr \(r.solution ?? .nan)")137            // cross-sheet chain: 2 × Profit!B7 = 10 000 by changing the price → price = (5000 + 8000)/1000 + 15138            let c = try expectUntouched(m, isolation, "cross-sheet") {139                try GoalSeek.run(engine: m.engine, setCell: m.cell("B6", sheet: 2), toValue: 10_000, byChanging: m.cell("B1", sheet: 1), options: options(isolation))140            }141            #expect(c.converged && isClose(c.solution ?? .nan, 28, rel: 1e-10))142        }143    }144145    @Test func failuresRestoreTheOriginalAndSayWhy() throws {146        for isolation in WhatIfIsolation.allCases {147            let m = Model()148            m.set("H1", "3"); m.set("H2", "=H1^2+1"); m.set("H3", "=IF(H1>5,10,-10)"); m.set("H4", "abc"); m.set("H5", "=H1*H1")149            m.seedHistory()150            let none = try expectUntouched(m, isolation, "no real root") {151                try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 0, byChanging: m.cell("H1"), options: options(isolation))152            }153            #expect(!none.converged && !none.committed && none.message.contains("No solution"))154            #expect(none.residual.isNaN || none.residual >= 1)        // x² + 1 never gets below 1155            #expect(m.num("H1") == 3)156            let jump = try expectUntouched(m, isolation, "discontinuity") {157                try GoalSeek.run(engine: m.engine, setCell: m.cell("H3"), toValue: 0, byChanging: m.cell("H1"), options: options(isolation))158            }159            #expect(!jump.converged && jump.message.contains("discontinuity"))160            var commit = options(isolation); commit.commit = true161            let stillNone = try expectUntouched(m, isolation, "failed commit") {162                try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 0, byChanging: m.cell("H1"), options: commit)163            }164            #expect(!stillNone.committed)165            // precondition failures never touch anything (not even in-place)166            let formula = try expectUntouched(m, isolation, edits: false, "formula input") {167                try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 5, byChanging: m.cell("H5"), options: options(isolation))168            }169            #expect(!formula.converged && formula.message.contains("formula") && formula.evaluations == 0)170            let text = try expectUntouched(m, isolation, edits: false, "text input") {171                try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 5, byChanging: m.cell("H4"), options: options(isolation))172            }173            #expect(!text.converged && text.message.contains("number"))174            let nan = try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: .nan, byChanging: m.cell("H1"), options: options(isolation))175            #expect(!nan.converged)176            #expect(throws: WhatIfError.self) { try GoalSeek.run(engine: m.engine, setCell: ModelCell(sheet: 5, row: 0, col: 0), toValue: 1, byChanging: m.cell("H1")) }177            #expect(throws: WhatIfError.self) { try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 1, byChanging: ModelCell(sheet: -1, row: 0, col: 0)) }178            // a set cell that is text everywhere cannot be driven anywhere179            let dead = try expectUntouched(m, isolation, "text output") {180                try GoalSeek.run(engine: m.engine, setCell: m.cell("H4"), toValue: 1, byChanging: m.cell("H1"), options: options(isolation))181            }182            #expect(!dead.converged && dead.solution == nil)183        }184    }185186    @Test func boundsStartValuesAndSecantFallback() throws {187        let m = Model()188        m.set("H1", "1"); m.set("H2", "=H1^2"); m.set("H3", "=(H1-3)^2")189        var o = GoalSeekOptions()190        o.lowerBound = 0191        let pos = try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 4, byChanging: m.cell("H1"), options: o)192        #expect(pos.converged && isClose(pos.solution ?? .nan, 2, rel: 1e-9))193        m.set("H1", "-1")194        o.lowerBound = nil; o.upperBound = 0195        let neg = try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 4, byChanging: m.cell("H1"), options: o)196        #expect(neg.converged && isClose(neg.solution ?? .nan, -2, rel: 1e-9))197        // already there198        m.set("H1", "2")199        let here = try GoalSeek.run(engine: m.engine, setCell: m.cell("H2"), toValue: 4, byChanging: m.cell("H1"))200        #expect(here.converged && here.method == "initial value" && here.solution == 2 && here.evaluations == 1 && here.iterations == 0)201        // a double root never changes sign: bracketing fails, the secant method takes over202        m.set("H1", "1")203        let touch = try GoalSeek.run(engine: m.engine, setCell: m.cell("H3"), toValue: 0, byChanging: m.cell("H1"))204        #expect(touch.converged && touch.method == "secant" && touch.bracketLower == nil)205        #expect(abs((touch.solution ?? .nan) - 3) < 1e-4 && abs(touch.residual) <= 1e-9)206        // blank changing cell starts from 0207        m.set("K2", "=K1*4-10")208        let blank = try GoalSeek.run(engine: m.engine, setCell: m.cell("K2"), toValue: 0, byChanging: m.cell("K1"))209        #expect(blank.converged && blank.startValue == 0 && isClose(blank.solution ?? .nan, 2.5, rel: 1e-10))210        #expect(m.wb.sheets[0].cell(0, 10) == nil)      // K1 still does not exist211        // a looser tolerance is honoured, a tiny iteration budget is reported as failure212        var loose = GoalSeekOptions(); loose.tolerance = 1e-3213        let l = try GoalSeek.run(engine: m.engine, setCell: m.cell("B4"), toValue: 1500, byChanging: m.cell("B3"), options: loose)214        #expect(l.converged && abs(l.residual) <= 1.5)215        var tiny = GoalSeekOptions(); tiny.maxIterations = 1216        m.set("L1", "1"); m.set("L2", "=L1^3+L1^5")217        let t = try GoalSeek.run(engine: m.engine, setCell: m.cell("L2"), toValue: 777, byChanging: m.cell("L1"), options: tiny)218        #expect(!t.converged && t.solution != nil && m.num("L1") == 1)219    }220221    @Test func commitIsOneUndoableEdit() throws {222        for isolation in WhatIfIsolation.allCases {223            let m = Model()224            m.seedHistory()225            let before = snapshot(m.engine)226            let count = m.engine.changeCount227            var o = options(isolation); o.commit = true228            let r = try GoalSeek.run(engine: m.engine, setCell: m.cell("B7", sheet: 1), toValue: 5000, byChanging: m.cell("B2", sheet: 1), options: o)229            #expect(r.converged && r.committed && isClose(r.solution ?? .nan, 1300, rel: 1e-10))230            #expect(isClose(m.num("B2", sheet: 1), 1300, rel: 1e-10) && isClose(m.num("B7", sheet: 1), 5000, rel: 1e-9))231            #expect(m.engine.undoLabel == "Goal Seek" && !m.engine.canRedo)232            if isolation == .clone { #expect(m.engine.changeCount == count + 1) }233            m.engine.undo()234            #expect(snapshot(m.engine) == before)235            #expect(m.engine.undoLabel == "Typing")236        }237    }238}239
Files main
Menu

Theme