dychechris's avatar
dychechris
New Member
9 years ago

Loan Calculator

This report uses three what if parameters to calculate the monthly payments, total interest and the total amount payable for a loan. Simply play with the sliders to see how it affects the payments.

 

PLEASE NOTE This calculator is just to demonstrate the What If parameters and should not be used for real financial calculations.

3 Replies

  • sbone's avatar
    sbone
    Frequent Visitor
    8 years ago

    I'm trying to recreate this exact function. Would it be possible for you to share the Power Bi file?

    Thank you!!!!!!!!

     

    Stephen

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Dychechris....could you kindly share the loan calculation pbix file and happy new sir.

  • calculating's avatar
    calculating
    Frequent Visitor
    3 years ago

    Assuming you have a table named 'Loans' with the following columns: 'LoanAmount,' 'InterestRate,' 'LoanTerm,' and 'StartDate,' here's how you can use loan calculator bot the monthly payment and total interest paid:

    To calculate the monthly payment, you can use the PMT function in DAX. The formula might look like this:

     

     

     

     

     

     

     

     

     

     

     

    MonthlyPayment = -PMT(Loans[InterestRate] / 12, Loans[LoanTerm], Loans[LoanAmount])

     

     

     

     

     

     

     

     

     

     

     

    In this formula:

    • Loans[InterestRate] / 12 calculates the monthly interest rate (assuming the annual interest rate is in decimal form).
    • Loans[LoanTerm] is the total number of monthly payments.
    • Loans[LoanAmount] is the principal loan amount

    To calculate the total interest paid over the life of the loan , you can use the following DAX formula:

     

     

     

     

     

     

     

     

     

     

     

    TotalInterestPaid =
VAR MonthlyPayment = -PMT(Loans[InterestRate] / 12, Loans[LoanTerm], Loans[LoanAmount])
RETURN
    (MonthlyPayment * Loans[LoanTerm]) - Loans[LoanAmount]

     

     

     

     

     

     

     

     

     

     

     

    In this formula:

    • MonthlyPayment is calculated using the same PMT function as in the previous formula.
    • (MonthlyPayment * Loans[LoanTerm]) - Loans[LoanAmount] calculates the total interest paid by subtracting the initial loan amount from the sum of all monthly payments.

    Once you've created these DAX measures, you can use them in your Power BI visuals to display the monthly payments and total interest paid for each loan.

    Source:    https://chat.openai.com/