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?

user_7237224 · Answer

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()




Loan amount:



 
Fixed price:



 
Monthly interest:



 
Initial fee:



 
Monthly fee:





 
 
Duration: 

Saldo Primo: 

Saldo Ultimo: 

RunningAdm: 

RunningInterest: 

TotalCost: 

Totalpay: 

FixedPrice: 

ExtraOrdinaryInstallment:

Conversion from VBA to JS - wrong output

1 Answers

Changes

a.

b.

c.