locked
Loan Calculation RRS feed

  • Question

  • Please how do I program this in Excel VBA

    Cell C13, Enter Loan Amount as 1500 (prompt for value entered)
    Cell C14, Enter processing fee in %
    Cell C15, Enter insurance in %
    Cell C16, Enter Interest rate in %
    Cell C17,
    Cell B1, Enter Period as 36 (prompt for value entered)
    Cell C1, One-month moratorium (prompt for yes or no) if no then 0
    Cell D1, period as 12 (prompt for value entered)
    Cell E1, Calculate Principal.
    Cell B5, Enter initial loan and interest


    If month of loan is < Aug 2013
    Set Processing Fee = 3%
    Insurance = 2.25%
    Interest rate = 1.75%

    Else if month of loan is Aug 2013 <= month of loan < Jun 2015
    Set Processing Fee = 1.75%
    Insurance = 2%
    Interest rate = 1.75%

    Else if month of loan is >= Jun 2015
    Set Processing Fee = 3%
    Insurance = 2%
    Interest rate = 2%

    If month of loan is >= Jun 2015 then set no one month-moratorium else set one month-moratorium to 0.

    Formula is Loan Amount = Processing Fee *Loan Amount + Insurance *Loan Amount +(1+ Interest rate)^Period

    Principal = (Loan Amount * Processing Fee)+(Loan Amount * Insurance Fee)+ Loan Amount + one-month moratorium

    one-month moratorium = ((Loan Amount + (Loan Amount * Processing Fee)+(Loan Amount * Insurance Fee) * Int_rate ) )

    monthly pmt = principal * ((Interest rate *(1 + Interest rate)^period)/(1+Interest rate)^-1

    initial loan and interest = monthly pmt * period





    Set period as range from 1 to 36 months (differences of 1) and Loan Amount as range from 100 to 12000 (difference of 50)
    Sunday, May 15, 2016 10:06 PM

All replies

  • For all of these:

    Cell C13, Enter Loan Amount as 1500 (prompt for value entered)
    Cell C14, Enter processing fee in % 
    Cell C15, Enter insurance in %
    Cell C16, Enter Interest rate in %
    Cell C17,
    Cell B1, Enter Period as 36 (prompt for value entered)
    Cell D1, period as 12 (prompt for value entered)

    use code like this for each one

    Range("C13").Value = CDbl(Inputbox("Enter Loan Amount"))

    For this one:

    Cell C1, One-month moratorium (prompt for yes or no) if no then 0

    use code like

    Range("C1").Value = - (Msgbox("One-month moratorium?",vbYesNo) = vbYes) * 1 

    For the others, just set up cell formulas to do what you want.


    Monday, May 16, 2016 5:55 PM