R Shiny App: Annuity & Amortization Schedule Calculator

RDataGuy

02/15/2021

Introduction

In December, I decided I wanted to try and see if I could figure out how to build a Shiny App that would allow me to either calculate a Loan Amoritzation schedule (e.g. auto, mortgage) or would allow me to determine the future value of an investment. I looked around online to see if anyone else had tried this and the closest example I could find was done was a loan amortization calculator by Guangming Lang of MasterR.org (https://masterr.org/r/calculate-mortgage-payment-schedule/). I decided to use some of his code as a starting point for the loan portion of of the Shiny application we’ll build today. I then added the annuity portion and the pie chart. If you want to learn more about the calculations I used for the annuity portion of the code here is a a reference I used to come up with the calculations.

The Shiny Web Application

Before we get started, I just want to mention that I’m not going to explain how I’m going the build the app, rather I’m just going to share the code. I covered the basics of building the code on RStudio.cloud and hosting a Shiny Application on ShinyApps.io back in Lesson 9. If you want more information on how to setup and host a Shiny App, please go do that lesson first before you attempt this example.

Build the Application

I like to use a single RScript file format that combines the user interface and server information into a single file called app.R. Here’s what my finished code looks like.

# HEADER --------------------------------------------
#
# Author: RDataGuy
# Copyright (c) RDataGuy, 2019
# Email:  rdataguy@gmail.com
# 
# Date: 2020-12-20
#
# Shiny App Name: Annuity & Amortization Schedule Calculator
#
# Shiny App Description: Generate a table showing future value of an an
# investment (e.g. loan, annuity)
#
# Notes:
# I found original code on: https://masterr.org/r/calculate-mortgage-payment-schedule/
# and then decided to make modifications to it to include not just loan payments but also
# annuity growth calculations.
#
# This is a single file Shiny App.
#

# LOAD LIBRARIES ------------------------------------
library('shiny')
library('tidyverse')
library('lubridate')
library('plotly')

options(scipen = 999)

# USER INTERFACE ------------------------------------
# Define UI for application that calculates a table showing the future value of
# an investment.

ui <- fluidPage(
    
    # Application title
    titlePanel("Annuity & Amortization Schedule Calculator"),
    
    # Sidebar with inputs for the investment calculator
    sidebarLayout(
        sidebarPanel(
            
            radioButtons("type",
                         "Investment Type?",
                         choices = c('Loan','Annuity'),
                         selected = 'Loan'),
            
            numericInput("amount",
                         "Initial Amount ($):",
                         10000),
            
            numericInput("APR",
                         "Interest Rate (%):",
                         2.5),
            
            numericInput("term",
                         "Term (# of months)",
                         72),
            
            dateInput("start_date",
                      "Start Date",
                      today(),
                      format = "yyyy-mm-dd"),
            
            numericInput("down_pmt",
                         "Initial Down Payment (if Loan)",
                         2000),
            
            numericInput("dep",
                         "Monthly Deposit (if Annuity)",
                         0),
          
            br(), # adds a blank line
            actionButton("do", "Generate!"),
            width = 3
        ),
        
        # Show a table of investment using the Server's output called "schedule"
        mainPanel(
            splitLayout( tableOutput("schedule"),
                         plotlyOutput("pie_chart")
            ),
            width = 9
        )
    )
)

# SERVER INFORMATION --------------------------------
# Define server logic required to display table & pie chart
server <- function(input, output) {
    
    amortize = function(loan_amt, down_pmt, start_date, term, APR) {
        # calculate simple monthly rate 
        monthly_rate = APR / 12
        # start_date = as.Date(start_date, "%Y-%m-%d")
        
        # calculate (constant) contractual monthly payment amount
        # derived from the present value formula for annuities
        # https://en.wikipedia.org/wiki/Annuity
        r = (1 + monthly_rate) ^ term - 1
        pay = (loan_amt-down_pmt) * monthly_rate * (r + 1) / r
        
        # initialize output variables
        interest = principal = payment = balance = vector("numeric", term)
        
        # calc amortization schedule
        outstanding_principal = loan_amt - down_pmt
        for (i in 1:term) {
          
            intr = outstanding_principal * monthly_rate
            if(outstanding_principal < payment[i]){
                prnp = outstanding_principal
                outstanding_principal = 0
                final = TRUE
                
            }else{
              
                prnp = pay - intr
                outstanding_principal = outstanding_principal - prnp
                final = FALSE
            }
            
            interest[i]  = round(intr,2)
            principal[i] = round(prnp,2)
            payment[i] = round((prnp + intr),2)
            balance[i] = round(outstanding_principal,2)
        }
        
        data.frame('Month' = 1:term, 'Date' = ymd(start_date) %m+% months(1:term), 'Interest' = interest, 'Principal' = principal, 'Payment' = payment, 'Balance' = balance) 
    }
    
    annuity = function(initial_amt, start_date, dep, term, APR) {
        # calculate simple monthly rate 
        monthly_rate = APR / 12
        
        # calculate (constant) contractual monthly payment amount
        # derived from the present value formula for annuities
        # https://en.wikipedia.org/wiki/Annuity

        deposit = dep
        
        # initialize output variables
        interest = deposit = balance = vector("numeric", term)
        
        # calc amortization schedule
        outstanding_principal = initial_amt
        for (i in 1:term) {
            intr = outstanding_principal * monthly_rate
            prnp = dep + intr
            outstanding_principal = outstanding_principal + prnp
            
            interest[i]  = round(intr,2)
            deposit[i] = round(dep,2)
            balance[i] = round(outstanding_principal,2)
        }
        
        data.frame('Month' = 1:term, 'Date' = seq(as.Date(start_date), length.out = term, by="months"), 'Interest' = interest, 'Deposit' = deposit, 'Balance' = balance) 
    }

    # Use an action button as an event to generate the list of random numbers
    schedule_data <- eventReactive(input$do, {  
        
        # Determine if loan or annuity and then calcluate
        if (input$type == "Loan"){
          
            sched <- amortize(loan_amt = input$amount, down_pmt = input$down_pmt, start_date = input$start_date, term = input$term, APR = input$APR/100)
            sched$Date <- format(sched$Date,'%Y-%m-%d')
            return(sched)
            
        }else{
          
            sched <- annuity(initial_amt = input$amount, start_date = input$start_date, dep = input$dep, term = input$term, APR = input$APR/100)
            sched$Date <- format(sched$Date,'%Y-%m-%d')
            return(sched)
        }
    })
    
    pie_data <- eventReactive(input$do, {  
        
        # Determine if loan or annuity and then calcluate
        if (input$type == "Loan"){
          
            sched <- amortize(loan_amt = input$amount, down_pmt = input$down_pmt, start_date = input$start_date, term = input$term, APR = input$APR/100)
            sched$Date <- format(sched$Date,'%Y-%m-%d')
            name = c("Principal", "Down Payment","Interest")
            start_bal = input$amount
            final_bal = sched$Balance[input$term]
            principal = sum(sched$Principal)
            interest = sum(sched$Interest)
            values = c(principal , input$down_pmt, interest )
            dat = data.frame(cbind(name, values))
            dat$values <- as.numeric(dat$values)
            total <- sum(dat$values)
            dat$percents <- 100*(dat$values / total)
            dat = dat %>% dplyr::arrange(desc(percents)) 
            
            return(dat)

        }else{
          
            sched <- annuity(initial_amt = input$amount, start_date = input$start_date, dep = input$dep, term = input$term, APR = input$APR/100)
            sched$Date <- format(sched$Date,'%Y-%m-%d')
            name = c("Initial Amount", "Contributions", "Growth")
            start_bal = input$amount
            final_bal = sched$Balance[nrow(sched)]
            contrib = sum(sched$Deposit)
            growth = final_bal - start_bal - contrib
            values = c(input$amount, contrib , growth )
            dat = data.frame(cbind(name, values))
            dat$values <- as.numeric(dat$values)
            total <- sum(dat$values)
            dat$percents <- 100*(dat$values / total)
            dat = dat %>% dplyr::arrange((percents)) 
            return(dat)
        }
    })
    
    # Output the schedule only AFTER the "Generate!" button is pressed
    output$schedule <- renderTable({
        schedule_data()
    }, rownames = FALSE, colnames = TRUE)
    
    output$pie_chart <- renderPlotly({
        # store rendered pie_data() as a tibble
        data <- tibble(pie_data())
        fig <- plot_ly(data, labels = ~name, values = ~values, type = 'pie')
        fig <- fig %>% layout(title = paste0(input$type, ' Details', sep=""),
                              xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
                              yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
    })
}

# RUN APPLICATION ----------------------------------
shinyApp(ui = ui, server = server)

Test our Web Application

Okay! Let’s test it! To test your application, click on the “Run App” button.

You should see a web page pop up. Try clicking on the “Generate!” button.

If you Generated a Loan Schedule, you should see output similar to this:

If you generated an Annuity Schedule, you should see output similar to this:

Deploy our Web Application

Alright! Time to deploy this baby! Rather than re-hash the steps to deploy this application, check out my previous example on how to setup a Shinyapps.io account and deploy your application. Skip down to “Step #5: Deploy our Web Application”

After you deploy your application the browser window should pop up with your newly deployed web application. Here’s a link to my final Annuity & Amortization Schedule Calculator App for you check out.

Conclusion

Overall, I was pretty happy with this Shiny App, it does exactly what I planned it would do. One idea I had for a possible improvement was to create a way to export the schedule & chart to a PDF document. I had developed the code but ran into issues with creating the PDF while hosting the app on Shinyapps.io and I can’t afford to run RStudio Connect where you get that capability. Apparently, you cannot render PDF documents because you cannot write temporary files to Shinyapps.io. If you figure out a way to do this, I would be interested in seeing your solution!

I really enjoyed building this application, I hope you have some fun building it as well.

Have fun coding!

RDataGuy

Get the complete code for this app on GitHub (Click link)!