31:T4b5,# loan validator 🕵️ (dev 📓) 💭 *The goal here is to build a comprehensive tool that can be used to **scenario-out and validate** loans* 💳 *and mortgages* 🧱🏡 💭 *This may take an upload of payments and validate balances* ⚖️*, allowing for any refinancing* 🤝*, missed payments* ❌*, etc.* 💭 *Some of this might require detailed product-specifics about additional charges for instance* 🤯* and **might warrant a two-way effort**. Thats ok!* 🤝☑️🌎 **This model is under construction 🚧** There are [known issues](https://github.com/declann/calculang-loan-validator/issues). ⚠️ *The assumptions, methodology and limitations of a model should be carefully considered for any purpose you apply it to. I haven't even listed these here, nor otherwise documented the model fully/clearly.* **Proceed with caution** 🙏 ⚠️ *This is not a complete model/narrative and you should not rely on it.* ⚠️ *Not recommended to enter real personal or sensitive details. **Contact me with real-world calculation or model requirements instead.*** 🗣✅ ⚠️ *This validator/scenario tool isn't/won't be financial advice etc.* ### some initial calcs ('base')32:[] 2f:{"title":"loan validator 🕵️ (dev 📓)","cells":[{"id":0,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":53,"value":"viewof principal_input = Inputs.range([0, 500000], {label: \"Principal 💰 €\", step:10000, value:100000})","pinned":false,"hidden":false,"mode":"ojs"},{"id":22,"value":"viewof interest_pc_input = Inputs.range([-5, 20], {label: \"Interest %\", step: 0.1, value: 4}) // neg interest is probably contrary and not tested well","pinned":false,"hidden":false,"mode":"ojs"},{"id":38,"value":"viewof term_input = Inputs.select([\"25\", \"15\", \"10\", \"5\", \"30\", \"35\", \"40\", \"1\", \"480\"], {label: \"Term (years)\", value: 10})","pinned":false,"hidden":false,"mode":"ojs"},{"id":113,"value":"md`The repayment amount over the term is **€${repayment_amount.toLocaleString('US-en', { maximumFractionDigits: 2 })}** per annum.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":297,"value":"md`Here is a projection of the repayments, assuming all repayments are made and made on time. The interest is in orange; if the interest rate is positive the area covered in orange is representative of the **cost of capital = €${COC_base.toLocaleString('US-en', { maximumFractionDigits: 2 })}**\n\nThe loan balance is overlayed in red.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":526,"value":"v = { spec.datasets = {projection: [...projection_base]}; // needed to init correctly, because of..?\n return vega(spec)}","pinned":false,"hidden":false,"mode":"ojs"},{"id":491,"value":"md`### scenario: ↑${delta_i_pc_input}% Δ interest rate in year ${delta_i_year_in}` // todo conditional direction arrows","pinned":false,"hidden":false,"mode":"ojs"},{"id":744,"value":"viewof delta_i_year_in = Inputs.range([1, term_input-2], {label: \"scenario year\", step: 1, value: 5})","pinned":false,"hidden":false,"mode":"ojs"},{"id":747,"value":"viewof delta_i_pc_input = Inputs.range([-5, 8], {label: \"Δ interest %\", step: 0.1, value: 3})","pinned":false,"hidden":false,"mode":"ojs"},{"id":725,"value":"viewof checkboxes_delta_i = Inputs.checkbox([\"immediate refinancing\"], {label: \"\", value: [\"immediate refinancing\"], disabled:[\"immediate refinancing\"]})\n\n// interest relief = interest is neither paid nor charged. It behaves like interest was paid (so balance maintained constant, repayments increase with refinancing (o/w just 1 = additional payment?). COC shouldn't change","pinned":false,"hidden":false,"mode":"ojs"},{"id":792,"value":"md`The new **cost of capital = €${projection_d_i.reduce((a,v) => a + v.interest_repayment,0).toLocaleString('US-en', { maximumFractionDigits: 2 })}** ${(COC_base - COC_d_i) > 0 ? '↓' : '↑'}${Math.abs(COC_base - COC_d_i).toLocaleString('US-en', { maximumFractionDigits: 2 })}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":759,"value":"x = { var spec3 = Object.assign({}, spec); spec3.datasets = {projection: [...projection_d_i]}; // needed to init correctly, because of..?\n return vega(spec3)}","pinned":false,"hidden":false,"mode":"ojs"},{"id":768,"value":"*todo comments on the new repayment amount (where refinanced) or longer duration (where not refinanced)*\n\n*todo some checks?*","pinned":false,"hidden":false,"mode":"md"},{"id":487,"value":"### scenario: X=topup or overpayment paid in year Y\n\n*w. immediate refinancing default on*\n\n*todo*","pinned":false,"hidden":false,"mode":"md"},{"id":387,"value":"md`### scenario: skipped payment in year ${missed_repayment_year_in}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":421,"value":"viewof missed_repayment_year_in = Inputs.range([-10, term_input], {\n label: \"skipped payment\",\n step: 1,\n value: 5\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":435,"value":"viewof checkboxes_skip = Inputs.checkbox([\"immediate refinancing\", \"skip interest\", \"interest relief agreed (comment)\"], {label: \"\", value: [\"immediate refinancing\", \"skip interest\"], disabled:[\"immediate refinancing\", \"interest relief agreed (comment)\"]})\n\n// interest relief = interest is neither paid nor charged. It behaves like interest was paid (so balance maintained constant, repayments increase with refinancing (o/w just 1 = additional payment?). COC shouldn't change","pinned":false,"hidden":false,"mode":"ojs"},{"id":414,"value":"w = { var spec2 = Object.assign({}, spec); spec2.datasets = {projection: [...projection_A]}; // needed to init correctly, because of..?\n return vega(spec2)}","pinned":false,"hidden":false,"mode":"ojs"},{"id":575,"value":"md`The new **cost of capital = €${projection_A.reduce((a,v) => a + v.interest_repayment,0).toLocaleString('US-en', { maximumFractionDigits: 2 })}** ${(COC_base - COC_A) > 0 ? '↓' : '↑'}${Math.abs(COC_base - COC_A).toLocaleString('US-en', { maximumFractionDigits: 2 })}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":716,"value":"*todo comments on the higher repayment amount (where refinanced) or longer duration (where not refinanced)*","pinned":false,"hidden":false,"mode":"md"},{"id":673,"value":"skip_interest ? md`An additional **€${(capital_A - capital_base).toLocaleString('US-en', { maximumFractionDigits: 2 })}** ${skip_interest ? \"of skipped interest\" : \"\"} is borrowed and repaid.\n\n*(the only way to see this extra cost visually RN is via the jump in the balance overlay line)*` : md``","pinned":false,"hidden":false,"mode":"ojs"},{"id":274,"value":"md`\n## calculang model code 🧙\n\n✨ *No tool for secrets* ✨ | *[simpler times v334](https://observablehq.com/@declann/loan-validator-dev@334)* | *[skip scenario v733](https://observablehq.com/@declann/loan-validator-dev@733)*\n\n~~~js\n${calculang_code[0].source}\n~~~\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":740,"value":"### 🚧 workings","pinned":false,"hidden":false,"mode":"md"},{"id":652,"value":"checkboxes_skip","pinned":false,"hidden":false,"mode":"ojs"},{"id":684,"value":"// if we skip the capital but not the interest, then the loan balance shouldn't move\nbalance_constant_chk = +loan\n .balance({ year_in: missed_repayment_year_in, missed_repayment_year_in, term_in:+term_input, i_in, principal_in: principal_input, skip_interest_in:skip_interest })\n .toFixed(2)\n-loan\n .balance({ year_in: missed_repayment_year_in-1, missed_repayment_year_in, term_in:+term_input, i_in, principal_in: principal_input, skip_interest_in:skip_interest })\n .toFixed(2) == 0 ? '✅' : (skip_interest ? \"␕\" : '❌')","pinned":false,"hidden":false,"mode":"ojs"},{"id":656,"value":"skip_interest = checkboxes_skip.indexOf(\"skip interest\") != -1 // only relates to skip scenario","pinned":false,"hidden":false,"mode":"ojs"},{"id":472,"value":"COC_A = projection_A.reduce((a,v) => a + v.interest_repayment,0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":789,"value":"COC_d_i = projection_d_i.reduce((a,v) => a + v.interest_repayment,0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":475,"value":"COC_base = projection_base.reduce((a,v) => a + v.interest_repayment,0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":598,"value":"capital_base = projection_base.reduce((a,v) => a + v.capital_repayment,0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":582,"value":"capital_A = projection_A.reduce((a,v) => a + v.capital_repayment,0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":449,"value":"## projections","pinned":false,"hidden":false,"mode":"md"},{"id":562,"value":"viewof start_minus_1 = Inputs.checkbox([\"start minus 1?\"], {value:[\"start minus 1?\"]})","pinned":false,"hidden":false,"mode":"ojs"},{"id":567,"value":"start = start_minus_1.length ? -1 : 0","pinned":false,"hidden":false,"mode":"ojs"},{"id":251,"value":"projection_base = _.range(start,+term_input+1).map(\n (year_in) => ({\n year_in,\n balance: +loan\n .balance({ year_in, term_in:+term_input, i_in, principal_in: principal_input })\n .toFixed(2),\n capital_repayment: +loan\n .capital_repayment({ year_in, term_in:+term_input, i_in, principal_in: principal_input })\n .toFixed(2),\n interest_repayment: +loan\n .interest_repayment({ year_in, term_in:+term_input, i_in, principal_in: principal_input })\n .toFixed(2)\n })\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":755,"value":"projection_d_i = _.range(start,+term_input+1).map(\n (year_in) => ({\n year_in,\n balance: +loan\n .balance({ year_in, /*missed_repayment_year_in,*/ term_in:+term_input, i_in, principal_in: principal_input, d_i_year_in:delta_i_year_in, d_i_in:delta_i_pc_input/100 /*skip_interest_in:skip_interest*/ })\n .toFixed(2),\n capital_repayment: +loan\n .capital_repayment({ year_in, /*missed_repayment_year_in,*/ term_in:+term_input, i_in, principal_in: principal_input, d_i_year_in:delta_i_year_in, d_i_in:delta_i_pc_input/100 /*skip_interest_in:skip_interest*/ })\n .toFixed(2),\n interest_repayment: +loan\n .interest_repayment({ year_in, /*missed_repayment_year_in,*/ term_in:+term_input, i_in, principal_in: principal_input, d_i_year_in:delta_i_year_in, d_i_in:delta_i_pc_input/100 /*skip_interest_in:skip_interest*/ })\n .toFixed(2)\n })\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":398,"value":"projection_A = _.range(start,+term_input+1).map(\n (year_in) => ({\n year_in,\n balance: +loan\n .balance({ year_in, missed_repayment_year_in, term_in:+term_input, i_in, principal_in: principal_input, skip_interest_in:skip_interest })\n .toFixed(2),\n capital_repayment: +loan\n .capital_repayment({ year_in, missed_repayment_year_in, term_in:+term_input, i_in, principal_in: principal_input, skip_interest_in:skip_interest })\n .toFixed(2),\n interest_repayment: +loan\n .interest_repayment({ year_in, missed_repayment_year_in, term_in:+term_input, i_in, principal_in: principal_input, skip_interest_in:skip_interest })\n .toFixed(2)\n })\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"## appendix","pinned":false,"hidden":false,"mode":"md"},{"id":220,"value":"[GitHub repo](https://github.com/declann/calculang-loan-validator) for calculang models, [Issues](https://github.com/declann/calculang-loan-validator/issues), [Discussions](https://github.com/declann/calculang-loan-validator/discussions) and 🌟s 🤗","pinned":false,"hidden":false,"mode":"md"},{"id":330,"value":"sha = \"main\" // or \"main\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":64,"value":"viewof version = Inputs.button(\"refresh calculang model (*see comment)\", {}) // I think need a session id, because common version numbers means I can hit old cached responses between sessions","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"loan = require(`https://cdn.jsdelivr.net/gh/declann/calculang-loan-validator@${sha}/models/simple-loan.js?v=${version}`)","pinned":false,"hidden":false,"mode":"ojs"},{"id":523,"value":"spec = ({\n \"$schema\": \"https://vega.github.io/schema/vega-lite/v5.json\",\n \"width\": 600,\n \"data\": {\"name\": \"projection\"},\n \"transform\": [{\"fold\": [\"capital_repayment\", \"interest_repayment\", \"balance\"], \"as\": [\"key\", \"amount\"]} // update\n //{\"calculate\": \"abs(datum.value)\", \"as\": \"abs_value\"}\n ],\n \"encoding\": {\n \"x\": {\"field\": \"year_in\"},\n \"y\": {\"field\": \"amount\", \"type\": \"quantitative\"},\n },\n \"layer\": [\n {\n \"transform\": [{\"filter\": \"datum.key != 'balance'\"}],\n \"mark\": {\"type\": \"bar\", \"tooltip\": true},\n \"encoding\": {\n \"color\": {\"field\": \"key\", \"type\": \"nominal\"}\n }\n },\n {\n \"transform\": [{\"filter\": \"datum.key == 'balance'\"}],\n \"mark\": {\"type\": \"line\", \"tooltip\": true, \"point\": true},\n \"encoding\": {\n \"color\": {\"value\": \"red\"},\n \"y\": {\"field\": \"amount\", \"type\": \"quantitative\", \"title\": \"balance\"}\n }\n }\n ],\n \"resolve\": {\"scale\": {\"y\": \"independent\"}}\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":8,"value":"vega = { // credit to Mike Bostock: https://observablehq.com/@mbostock/hello-vega-embed\n const v = window.vega = await require(\"vega\");\n const vl = window.vl = await require(\"vega-lite\");\n const ve = await require(\"vega-embed\");\n async function vega(spec, options) {\n const div = document.createElement(\"div\");\n div.value = (await ve(div, spec, options)).view;\n div.value.addEventListener('click', (event, item) => { console.log(item.datum) }) // DN\n return div;\n }\n vega.changeset = v.changeset;\n return vega;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":256,"value":"i_in = interest_pc_input/100","pinned":false,"hidden":false,"mode":"ojs"},{"id":45,"value":"repayment_amount = +loan.repayment_amount({term_in:+term_input, i_in, principal_in:principal_input, year_in:1}).toFixed(2)","pinned":false,"hidden":false,"mode":"ojs"},{"id":282,"value":"calculang_code = sourcemap.sources.map((d, i) => ({d,i})).filter(d => d.d.indexOf('.cul.js') != -1).map(d => ({filename: d.d, source: sourcemap.sourcesContent[d.i]}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":278,"value":"sourcemap = fetch(`https://cdn.jsdelivr.net/gh/declann/calculang-loan-validator@${sha}/models/simple-loan.js.map?v=${version}`).then(response => response.json())","pinned":false,"hidden":false,"mode":"ojs"},{"id":232,"value":"## credits\n\nmade with 💖 and [calculang](https://github.com/calculang)","pinned":false,"hidden":false,"mode":"md"}],"files":"$W32","stdlib":"1","titling":"auto"}