Conversion from VBA to JS - wrong output

Question

I got the following code in VBA from my customer:

Sub calculate()
    Range("B1") = 0
    Range("B2") = 0
    Range("B3") = 0
    Range("B4") = 0
    Range("B5") = 0
    Range("B6") = 0
    Range("B7") = 0

    Dim Duration
    Dim SaldoPrimo
    Dim SaldoUltimo

    Duration = 0
    SaldoPrimo = 0
    SaldoUltimo = 0
    MonthInterestAmount = 0
    RunningAdm = 0
    RunningInterest = 0
    ExtraOrdinaryInstallment = 0


    LoanAmount = Range("F3")
    FixedPrice = Range("F4")
    MonthInterest = Range("F5")
    OpeningFee = Range("F6")
    MonthFee = Range("F7")
    SaldoPrimo = LoanAmount + OpeningFee

    Do
        Duration = Duration + 1
        MonthInterestAmount = SaldoPrimo * MonthInterest
        SaldoUltimo = SaldoPrimo + MonthFee + MonthInterestAmount - FixedPrice
        
        RunningAdm = RunningAdm + MonthFee
        RunningInterest = RunningInterest + MonthInterestAmount
        
        SaldoPrimo = SaldoUltimo
                
        If Duration > 400 Then Exit Sub
        
        SaldoTest = SaldoUltimo + (SaldoPrimo * MonthInterest) + MonthFee

    Loop Until SaldoTest < FixedPrice
    
    
    If SaldoUltimo > 0 Then
       
       MonthInterestAmount = SaldoPrimo * MonthInterest
       Duration = Duration + 1
       RunningAdm = RunningAdm + MonthFee
       RunningInterest = RunningInterest + MonthInterestAmount
       ExtraOrdinaryInstallment = MonthInterestAmount + MonthFee + SaldoUltimo
       Range("B11") = MonthInterestAmount + MonthFee + SaldoUltimo
    End If
    
    
    SaldoPrimo = 0
    SaldoUltimo = 0
        
        
    TotalCost = RunningAdm + RunningInterest + OpeningFee
    Totalpay = LoanAmount + TotalCost

    Range("B1") = Duration
    Range("B2") = SaldoPrimo
    Range("B3") = SaldoUltimo
    Range("B4") = RunningAdm
    Range("B5") = RunningInterest
    Range("B6") = TotalCost
    Range("B7") = Totalpay
    Range("B8") = FixedPrice
    Range("B9") = ExtraOrdinaryInstallment  
End Sub

Values: F3: 250000, F4: 3499, F5: 0.41, F6: 3499, F7: 99

I "translated" into JavaScript (and changed input/output from Excel cells to text-inputs (for input) divs with ids (for output)) into this:

function calculate()
{
    let Duration=0;
    let SaldoPrimo=0;
    let SaldoUltimo=0;
    let MonthInterestAmount = 0;
    let RunningAdm = 0;
    let RunningInterest = 0;
    let ExtraOrdinaryInstallment = 0;

    LoanAmount=parseFloat($('#input_loan_amount').val());
    FixedPrice=parseFloat($('#input_installment').val());
    MonthInterest=parseFloat($('#input_monthly_interest').val());
    OpeningFee=parseFloat($('#input_initial_fee').val());
    MonthFee=parseFloat($('#input_monthly_fee').val());
    SaldoPrimo=LoanAmount+OpeningFee;
    do
     {
         Duration++;
         MonthInterestAmout=SaldoPrimo*MonthInterest;
         SaldoUltimo=SaldoPrimo+MonthFee+MonthInterest-FixedPrice;
         RunningAdm=RunningAdm+MonthFee;
         RunningInterest=RunningInterest+MonthInterestAmount;
         SaldoPrimo=SaldoUltimo;
         if (Duration>400)
         {
             break;
         }
         SaldoTest=SaldoUltimo+(SaldoPrimo*MonthInterest)+MonthFee;
    }
    while (SaldoTest < FixedPrice);

    if (SaldoUltimo>0)
     {
         MonthInterestAmount=SaldoPrimo*MonthInterest;
         Duration++;
         RunningAdm=RunningAdm+MonthFee;
         RunningInterest=RunningInterest+MonthInterestAmount;
         ExtraOrdinaryInstallment=MonthInterestAmount+MonthFee+SaldoUltimo;
         $('#result').html((MonthInterestAmount+MonthFee+SaldoUltimo));
     }
    SaldoPrimo=0;
    SaldoUltimo=0;
    let TotalCost=RunningAdm+RunningInterest+OpeningFee;
    let Totalpay=LoanAmount+TotalCost;
    $('#Duration').html(Duration);
    $('#SaldoPrimo').html(SaldoPrimo);
    $('#SaldoUltimo').html(SaldoUltimo);
    $('#RunningAdm').html(RunningAdm);
    $('#RunningInterest').html(RunningInterest);
    $('#TotalCost').html(TotalCost);
    $('#Totalpay').html(Totalpay);
    $('#FixedPrice').html(FixedPrice);
    $('#ExtraOrdinaryInstallment').html(ExtraOrdinaryInstallment);
}

Values: $('#input_loan_amount').val: 250000, $('#input_installment').val: 3499, $('#input_monthly_interest').val: 0.41, $('#input_initial_fee').val: 3499, $('#input_monthly_fee').val: 99

Despite the same input from Excel and the text-inputs, the results are different. E.g. the Duration in Excel/VBA is 90, while it is 2 in JavaScript. Could it be that do/while in JS does not do the same as do/loop until in VBA?

What am I doing wrong?

You can improve the question by editing your question to include the sample values you used for both sets of code i.e. what values are in the range F3-F7 in the spreadsheet ? — Robin Mackenzie
– Robin Mackenzie, Commented Nov 9, 2021 at 11:44

Terry Lennox · Accepted Answer · 2021-11-09 15:11:55Z

2

A few changes should allow the JavaScript code to produce the same output as the Excel sheet.

The major change is converting the VB loop until to a JavaScript while () statement.

The others are just minor errors/typos.

Also added a JSFiddle here: https://jsfiddle.net/vxr04knq/1/ (updated)

Changes

a.

while (SaldoTest < FixedPrice);

to

while (!(SaldoTest < FixedPrice));

(Since the VB code loops until, which is the opposite of while). You could also change to while (FixedPrice <= SaldoTest).

b.

MonthInterestAmout=SaldoPrimo*MonthInterest;

to

MonthInterestAmount=SaldoPrimo*MonthInterest;

(Just a typo probably)

c.

SaldoUltimo=SaldoPrimo+MonthFee+MonthInterest-FixedPrice;

to

SaldoUltimo=SaldoPrimo+MonthFee+MonthInterestAmount-FixedPrice;

Since we want the MonthInterestAmount to be added, not the MonthInterest (rate).

Update: I've also updated to multiply the MonthInterest input by (1/100) to reflect the user inputting as a percentage.

Complete JavaScript / HTML code:

function calculate()
{
    let Duration=0;
    let SaldoPrimo=0;
    let SaldoUltimo=0;
    let MonthInterestAmount = 0;
    let RunningAdm = 0;
    let RunningInterest = 0;
    let ExtraOrdinaryInstallment = 0;

    LoanAmount=parseFloat($('#input_loan_amount').val());
    FixedPrice=parseFloat($('#input_installment').val());
    // Divide interest rate by 100 since user will enter a percentage. 
    MonthInterest=(1/100)*parseFloat($('#input_monthly_interest').val());
    OpeningFee=parseFloat($('#input_initial_fee').val());
    MonthFee=parseFloat($('#input_monthly_fee').val());
    SaldoPrimo=LoanAmount+OpeningFee;
    do
     {
         Duration++;
         MonthInterestAmount=SaldoPrimo*MonthInterest;
         SaldoUltimo=SaldoPrimo+MonthFee+MonthInterestAmount-FixedPrice;

         RunningAdm=RunningAdm+MonthFee;
         RunningInterest=RunningInterest+MonthInterestAmount;
         SaldoPrimo=SaldoUltimo;
         if (Duration>400)
         {
             break;
         }
         SaldoTest=SaldoUltimo+(SaldoPrimo*MonthInterest)+MonthFee;
    }
    while (!(SaldoTest < FixedPrice)); // VB: Loop Until SaldoTest < FixedPrice

    if (SaldoUltimo>0)
     {
         MonthInterestAmount=SaldoPrimo*MonthInterest;
         Duration++;
         RunningAdm=RunningAdm+MonthFee;
         RunningInterest=RunningInterest+MonthInterestAmount;
         ExtraOrdinaryInstallment=MonthInterestAmount+MonthFee+SaldoUltimo;
         $('#result').html((MonthInterestAmount+MonthFee+SaldoUltimo));
     }
    SaldoPrimo=0;
    SaldoUltimo=0;
    let TotalCost=RunningAdm+RunningInterest+OpeningFee;
    let Totalpay=LoanAmount+TotalCost;
    $('#Duration').html(Duration);
    $('#SaldoPrimo').html(SaldoPrimo);
    $('#SaldoUltimo').html(SaldoUltimo);
    $('#RunningAdm').html(RunningAdm);
    $('#RunningInterest').html(RunningInterest);
    $('#TotalCost').html(TotalCost);
    $('#Totalpay').html(Totalpay);
    $('#FixedPrice').html(FixedPrice);
    $('#ExtraOrdinaryInstallment').html(ExtraOrdinaryInstallment);
}

calculate()

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>


<label for="input_loan_amount">Loan amount:</label>
<input type="text" id="input_loan_amount" name="input_loan_amount" value="250000">
<br>
 
<label for="input_loan_amount">Fixed price:</label>
<input type="text" id="input_installment" name="input_installment" value="3499">
<br>
 
<label for="input_loan_amount">Monthly interest:</label>
<input type="text" id="input_monthly_interest" name="input_monthly_interest" value="0.41">
<br>
 
<label for="input_loan_amount">Initial fee:</label>
<input type="text" id="input_initial_fee" name="input_initial_fee" value="3499">
<br>
 
<label for="input_monthly_fee">Monthly fee:</label>
<input type="text" id="input_monthly_fee" name="input_monthly_fee" value="99">
<br>
<br>
 
 
<b>Duration: </b><i id="Duration"></i><br>
<b>Saldo Primo: </b><i id="SaldoPrimo"></i><br>
<b>Saldo Ultimo: </b><i id="SaldoUltimo"></i><br>
<b>RunningAdm: </b><i id="RunningAdm"></i><br>
<b>RunningInterest: </b><i id="RunningInterest"></i><br>
<b>TotalCost: </b><i id="TotalCost"></i><br>
<b>Totalpay: </b><i id="Totalpay"></i><br>
<b>FixedPrice: </b><i id="FixedPrice"></i><br>
<b>ExtraOrdinaryInstallment: </b><i id="ExtraOrdinaryInstallment"></i><br>
<br>
<button type="button" onclick="calculate()">Calculate</button>

edited Nov 9, 2021 at 15:11

answered Nov 9, 2021 at 13:23

Terry Lennox

30.9k5 gold badges37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Albert van Harten Over a year ago

Thank you, Terry. This is better, but it is not doing what I meant it to do: Now, the loop is running from 1 to 401 (or actually from 0 to 400), but is not calculating anything. Then it breaks due to if(Duration>400), while it actually should break at 90, since the debt comes below the "fixed price".

Terry Lennox Over a year ago

Can you check in the snippet above, or the JS Fiddle: jsfiddle.net/0g98kbt4/2, I'm getting a Duration of 90, the same as the Excel Sheet?

Albert van Harten Over a year ago

That is strange! It does give a Duration of 90 in the JSFiddle. Somehow I might be making a mistake. Perhaps somewhere with the monthly interest, which should be 0.0041 (as in your version) where a user normally enters 0.41 (as in percent). I will check out where I am going wrong this time, but I am sure your script is doing the correct thing. Albert

Terry Lennox Over a year ago

I've updated the fiddle: jsfiddle.net/vxr04knq/1 this takes an interest rate in %, so it should match up with your form.

Albert van Harten Over a year ago

Brilliant, Terry! Thank you so much! I might have made a mistake somehow while copying your code, because now it's working! And thanks for the updated Fiddle. Albert

|

Collectives™ on Stack Overflow

Conversion from VBA to JS - wrong output

1 Answer 1

Changes

a.

b.

c.

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Changes

a.

b.

c.

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related